SUMIF function

[krishnak]

Hi All,

I want to use the SUMIF function to sum up the product volumes in a large worksheet by the cost - let us say below 50, 51 to 100, 101 to 150, 150 and over. It is easy to write the conditional argument for the first and last cases - "<"&$B44 or ">149"&$B44, where B44 is the cell reference for the price.
For the condition of 51 to 100, I am now calculating the sum for all products costing less than 100, and then subtracting the sum of all the products costing less than 50.
Is there any way that I can achieve this in a single step - like ">50" &"And"&"<100"?
Thanks in advance for any suggestion.

- Krishna

[rory]

Not with SUMIF, no. If you have Excel 2007, you can use SUMIFS; otherwise you can use SUMPRODUCT:
=SUMPRODUCT(($A$2:$A$100>50)*($A$2:$A$100<100)*$B$2:$B$100)
for example, where column A has cost and B has volumes (or whichever way round you are working it)

[rory]

PS If you want bands of 50 like that, you could use a pivot table and group the cost in intervals of 50.

[Bob Phillips]

=SUMIF(A:A,"<=50")

=SUMIF(A:A,"<=100")-SUMIF(A:A,"<=50")

=SUMIF(A:A,"<=150")-SUMIF(A:A,"<=100")

etc., or use the previous values as the reducer.

[rory]

The OP did say he was already doing that but wanted to avoid it. It's probably easier than SUMPRODUCT though.

[Bob Phillips]

I read that as the reducing, my second point, and saw this as just as much a single step as SP, and tons more efficient.

[rory]

Oh OK - I guess we'll find out in due course.