aggregate counts into pre-defined price ranges

sarahrosenberg

Board Regular
Joined
Aug 27, 2002
Messages
190
What is the easiest way to compose a sumif-type command that will sum the counts into pre-defined ranges? (see my example below)
Book2
ABCDE
1Before
2After
3pricecount
4$208.07100pricecount
5$176.131235$100-$15089
6$165.76563$150-$2005360
7$174.88773$200-$250709
8$168.001588
9$115.6489
10$171.74268
11$178.08933
12$219.49582
13$226.5727
14
Sheet1
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

sarahrosenberg

Board Regular
Joined
Aug 27, 2002
Messages
190
Forgot to add...
one option I thought of was to add a column next to the price column that would include the range. Then, I could do a plain "sumif." But, I am not sure how to code this column, as there are about 15 price ranges.

e.g.
Book3
ABCDE
1NEWCOLUMN
2pricepricerange
3$220.25$200-$250
4$181.00$150-$200
5$186.00$150-$200
6$186.00$150-$200
7$251.25$250-$300
Sheet1
 

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
You can do this through an array formula. The first one and the third one work, but I am doing something wrong with the AND function on the second one. Anyway, when you enter these, you must use Ctrt+Shift+Enter. I'll try to fix the second one.


=SUM(IF(price<150,count,0))
=SUM(IF(AND(150<price,price<200),count,0))
=SUM(IF(price>200,count,0))

Still working on it.
 

sarahrosenberg

Board Regular
Joined
Aug 27, 2002
Messages
190
I used this formula (from the previous post):
=SUM(IF(AND(150 =SUM(IF(J2:J107<200,D2:D107,0)))))
and received an error.

My count data is in column D and my price data is in column J.
 

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348

ADVERTISEMENT

Ok, so I guess you can't use AND() in an array formula. Learn somethin new everyday. Here is the solution. Name your ranges price and count respectively.

=SUM((price<150)*count)
=SUM((price>150)*(price<200)*count)
=SUM((price>200)*count)

notice that I did not account for what to do when the price = 150 or price = 200, you can edit that part in according to how you want to set it up.

Let me know if it works for you.

Credit to John Walkenbach and Excel 2003 Power Programming with VBA.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Here's a sample,
Book1
ABCDEFG
3PriceCountPriceCount
4$208.071001001508989
5$176.13123515020053605360
6$165.76563200250709709
7$174.88773
8$168.001588
9$115.6489
10$171.74268
11$178.08933
12$219.49582
13$226.5727
Sheet1
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838

ADVERTISEMENT

Have you thought about using the SumProduct Function:

=SUMPRODUCT(--(A$1:A$10>=D3),--(A$1:A$10<E3),B$1:B$10)
Phone Number formatter.xls
ABCDEF
1$208.07100
2$176.131235LowerUpperCount
3$165.76563$100.00$150.0089
4$174.88773$150.00$200.005360
5$168.001588$200.00$250.00709
6$115.6489
7$171.74268
8$178.08933
9$219.49582
10$226.5727
Sheet3


[Edit: perhaps some day I will be as fast as you Brian!]
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Also with SumIf...
Book9
ABCDEFG
1
2pricecountpricerangetotalcount
3208.0710010015089
4176.1312351502005360
5165.76563200250709
6174.887732503000
71681588
8115.6489
9171.74268
10178.08933
11219.49582
12226.5727
Sheet1


G3, copied down:

=SUMIF($A$3:$A$12,">="&E3,$B$3:$B$12)-SUMIF($A$3:$A$12,">="&F3,$B$3:$B$12)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Brian from Maui said:
I also had a Sumif formula in there, but it doesn't display....... :devilish:

There is some lesson in there, no? Always report the formulas also outside the exhibit.

You're up late! :biggrin:

In order to compose redundant posts (see above)... :devilish:
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

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
Top