MrExcel Publishing
Your One Stop for Excel Tips & Solutions

still SUMIF question


Posted by rebecca on November 20, 2001 12:39 AM

Table as follow structure :

Title Value
A 1
A 2
A 3
B 1
B 2

Now, just want to calculate the item on "A" and value is between 2 and 3. How about SUMIF function ?


Posted by Aladin Akyurek on November 20, 2001 1:36 AM

Rebecca --

SUMIF cannot handle multiple conditions. SUMPRODUCT or an array formula can:

=SUMPRODUCT((A1:A100="A")*(B1:B100>=2)*(B1:B100<=3),(B1:B100))

will do what you want, that is, totaling all values for title A between 2 and 3 inclusive.

If you want just to count records where the title is "A" and the value between 2 and 3, use:

=SUMPRODUCT((A1:A100="A")*(B1:B100>=2)*(B1:B100<=3))

Aladin

===========