SUMIF
Posted by Manu on January 23, 2002 8:21 AM
How can I add a range in this formula, e.g. instead of going after values "<5", I would like to go after all values say ">0 but <1" or ">=16"(Kindly refer to formula for ready reference)
=SUMIF(A2:A100,"<5",B2:B100)
Thanks for your help,
Regards,
Posted by Aladin Akyurek on January 23, 2002 8:37 AM
=SUMIF(A2:A100,">"&E1,B2:B100)-SUMIF(A2:A100,">="&E2,B2:B100)
where E1 houses e.g., 0 and E2 1.
==============
Posted by Manu on January 23, 2002 8:44 AM
pardon me for being slow Aladin, I didn't quite get it, would you kindly spare a monet and just explain once again kindly,
Regards,
Posted by Aladin Akyurek on January 23, 2002 9:11 AM
Manu --
The syntax of SUMIF is:
SUMIF(Range1,Condition,Range2)
meaning sum each cell in Range2 if Condition holds for the corresponding cell in Range1. Sometimes is Range1 and Range2 are the one and same range. In such cases one is allowed to write
SUMIF(Range,Condition).
SUMIF by design cannot process multiple conditions. One case of multiple conditions is a (either inclusive or exclusive) BETWEEN condition for which SUMIF can still be used.
Lets say that Range1 is A2:A10 which houses numbers (e.g., dates or ordinary numbers) and Range2 B2:B10 which houses also numbers. I want sum each cell in B2:B10 if the corresponding cell in A2:A10 is grater than 1 and less than or equal to 16. As you see this is a between condition. Lets put 1 in E1 and 16 in E2. I can devise a SUMIF formula to achieve a multiconditional sum for this between case:
=SUMIF(A2:A10,">"&E1,B2:B10)-SUMIF(A2:A10,">"&E2)
The first SUMIF sums all cells in B2:B10 for all cells greater than 1 in A2:A10 (including thus the ones greater than 16). The second SUMIF sums all cells in B2:B10 for all cells greater than 16 in A2:A10. The substraction gives me exactly the sum I'am after.
You can do the same computation with an expensive SUMPRODUCT formula:
=SUMPRODUCT((A2:A10 > E1)*(A2:A10 <= E2), B2:B10)
Aladin
==========
Posted by Manu on January 23, 2002 9:18 AM
Thanks a bunck for your time Sir, that was helpful
