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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!]
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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