Grouping or sum if

[kumar1803]

Hello

I have some raw data and I would like to sum my profit in the following group:

1. summing all count of negative value in one group

2 summing all count of $0.00 to $500.00

3 Summing all count $501.00 to $1000.00

4. Summing all count $1001.00 to $1500.00

5 summing all count above $1500.00


I need to group the last column on the table below

Acquired Date	Stock Number	Contract Date	Make	Model	Year Model	Profit Commision Gross Profit
8/5/2015 0:00	3036	11/25/2015 0:00	GMC	Sierra 1500	2013	-4475.08
8/7/2015 0:00	3040	10/26/2015 0:00	Toyota	4Runner	2011	-3262.07
8/28/2015 0:00	3071	10/24/2015 0:00	Chevrolet	Silverado 2500HD	2014	-1645.61
8/6/2015 0:00	3039	11/23/2015 0:00	Chevrolet	Silverado 3500HD	2015	-708.04
8/19/2015 0:00	3060	11/14/2015 0:00	Chevrolet	Suburban	2012	-212.91
8/28/2015 0:00	3072	10/29/2015 0:00	GMC	Savana	2014	-148.42
8/25/2015 0:00	3067	11/10/2015 0:00	Chevrolet	Silverado 1500	2015	-34.47
8/25/2015 0:00	3068	11/12/2015 0:00	Jeep	Wrangler	2014	29.08
8/8/2015 0:00	3045	10/31/2015 0:00	Chevrolet	Suburban	2011	123.1
9/15/2015 0:00	3095	10/17/2015 0:00	RAM	1500	2013	147.61
8/18/2015 0:00	3059	11/6/2015 0:00	Jeep	Wrangler	2012	180.17
8/14/2015 0:00	3054	11/2/2015 0:00	Chevrolet	Silverado 1500	2014	382.78
8/5/2015 0:00	3038	10/16/2015 0:00	Jeep	Wrangler	2012	383.87
8/13/2015 0:00	3052	10/24/2015 0:00	Toyota	Tundra	2011	471.44
10/17/2015 0:00	3132	11/16/2015 0:00	Chevrolet	Silverado 1500	2014	533.42
8/11/2015 0:00	3048	10/10/2015 0:00	RAM	1500	2014	535.02
8/25/2015 0:00	3065	10/30/2015 0:00	GMC	Sierra 1500	2014	556.94
8/28/2015 0:00	3077	10/15/2015 0:00	Toyota	Tundra	2010	557.45
8/28/2015 0:00	3074	11/16/2015 0:00	RAM	2500	2015	637.24
8/11/2015 0:00	3049	11/3/2015 0:00	RAM	3500	2014	874.89
8/15/2015 0:00	3056	9/22/2015 0:00	Ford	Ranger	2010	910.55
8/21/2015 0:00	3064	11/16/2015 0:00	Dodge	Ram 2500	2009	980.45
9/9/2015 0:00	3090	10/26/2015 0:00	Kia	Sorento	2012	1211.32
10/15/2015 0:00	3126	10/24/2015 0:00	Ford	F-150	2011	1245.08
10/27/2015 0:00	3148	11/27/2015 0:00	Chevrolet	Silverado 1500	2014	1275.05
8/27/2015 0:00	3069	10/31/2015 0:00	Chevrolet	Silverado 2500HD	2012	1309.18
8/8/2015 0:00	3043	9/26/2015 0:00	Toyota	Tundra	2012	1417.28
10/8/2015 0:00	3121	11/25/2015 0:00	RAM	2500	2014	1476.1
9/24/2015 0:00	3106	10/12/2015 0:00	Ford	Explorer Sport Trac	2008	1600.83
8/20/2015 0:00	3061	11/7/2015 0:00	Chevrolet	Silverado 1500	2011	1636.89
9/9/2015 0:00	3091	10/30/2015 0:00	RAM	3500	2012	1637.35

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[sashi nair]

You can create a table and set filter for Gross field as per your requirement

 

[kumar1803]

I already tried that. I am trying to use sum or sum if formula to group them in the following order:

< 0.00
$0-$500.00
$501-$999.99
$100.00-$1499.99
above $1500.00

 

[kumar1803]

Can you help me build a look up formula in the range below?

< $0.00
$0.00-$499.99
$500.00-$999.99
$1000.00-$1499.99
Above $1500.00

Thanks

 

[Aladin Akyurek]

I already tried that. I am trying to use sum or sum if formula to group them in the following order:

< 0.00
$0-$500.00
$501-$999.99
$100.00-$1499.99
above $1500.00

Try something like...

=SUMIFS(G:G,G:G,"<0")

=SUMIFS(G:G,G:G,">0",G:G,"<=500")

=SUMIFS(G:G,G:G,">500",G:G,"<=1000")

=SUMIFS(G:G,G:G,">1000",G:G,"<=1500")

=SUMIFS(G:G,G:G,">1500")

 

[kumar1803]

Can you help me build a look up formula in the range below?

< $0.00
$0.00-$499.99
$500.00-$999.99
$1000.00-$1499.99
Above $1500.00

Thanks

 

[Aladin Akyurek]

Can you help me build a look up formula in the range below?

< $0.00
$0.00-$499.99
$500.00-$999.99
$1000.00-$1499.99
Above $1500.00

Thanks

Try...

=SUMIFS(G:G,G:G,"<0")

=SUMIFS(G:G,G:G,">0",G:G,"<500")

=SUMIFS(G:G,G:G,">=500",G:G,"<1000")

=SUMIFS(G:G,G:G,">=1000",G:G,"<1500")

=SUMIFS(G:G,G:G,">=1500")