Grouping or sum if

K

kumar1803

Board Regular
Joined
Jan 7, 2015
Messages
110
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

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
kumar1803 said:
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
Click to expand...

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")
 
Upvote 0
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
 
Upvote 0
kumar1803 said:
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
Click to expand...

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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
258
tezza
tezza
D
  • Solved
Help with rank and difference.
Replies
3
Views
215
Fluff
Fluff
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
588
awoohaw
awoohaw
M
  • Solved
Split single cell value in 2 columns.
Replies
7
Views
359
Fluff
Fluff
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
964
UnitedCloud01
U

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top