# aggregate counts into pre-defined price ranges

#### sarahrosenberg

##### Board Regular
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### sarahrosenberg

##### Board Regular
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
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
I used this formula (from the previous post):
=SUM(IF(AND(150 =SUM(IF(J2:J107<200,D2:D107,0)))))

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

#### yankee428

##### Active Member
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
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
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!]

##### MrExcel MVP
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)

#### Brian from Maui

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

You're up late!

##### MrExcel MVP
Brian from Maui said:
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)...

Replies
4
Views
680
Replies
1
Views
794
Replies
3
Views
235
Replies
4
Views
1K
Replies
1
Views
620

1,181,610
Messages
5,930,916
Members
436,766
Latest member
azex85

### 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.

### Which adblocker are you using?

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

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