aggregate counts into pre-defined price ranges

[sarahrosenberg]

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
	A	B	C	D	E
1	Before
2				After
3	price	count
4	$208.07	100		price	count
5	$176.13	1235		$100-$150	89
6	$165.76	563		$150-$200	5360
7	$174.88	773		$200-$250	709
8	$168.00	1588
9	$115.64	89
10	$171.74	268
11	$178.08	933
12	$219.49	582
13	$226.57	27
14
Sheet1

 

[sarahrosenberg]

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
	A	B	C	D	E
1		NEWCOLUMN
2	price	pricerange
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]

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]

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]

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]

Here's a sample,

Book1
	A	B	C	D	E	F	G
3	Price	Count		Price		Count
4	$208.07	100		100	150	89	89
5	$176.13	1235		150	200	5360	5360
6	$165.76	563		200	250	709	709
7	$174.88	773
8	$168.00	1588
9	$115.64	89
10	$171.74	268
11	$178.08	933
12	$219.49	582
13	$226.57	27
Sheet1

 

[PA HS Teacher]

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
	A	B	C	D	E	F
1	$208.07	100
2	$176.13	1235		Lower	Upper	Count
3	$165.76	563		$100.00	$150.00	89
4	$174.88	773		$150.00	$200.00	5360
5	$168.00	1588		$200.00	$250.00	709
6	$115.64	89
7	$171.74	268
8	$178.08	933
9	$219.49	582
10	$226.57	27
Sheet3

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

 

[Aladin Akyurek]

Also with SumIf...

Book9
	A	B	C	D	E	F	G
1
2	price	count			pricerange		totalcount
3	208.07	100			100	150	89
4	176.13	1235			150	200	5360
5	165.76	563			200	250	709
6	174.88	773			250	300	0
7	168	1588
8	115.64	89
9	171.74	268
10	178.08	933
11	219.49	582
12	226.57	27
Sheet1

G3, copied down:

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

 

[Brian from Maui]

I also had a Sumif formula in there, but it doesn't display.......

You're up late!

 

[Aladin Akyurek]

I also had a Sumif formula in there, but it doesn't display.......

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

You're up late!

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