Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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,


Check out our Excel Resources

Re: SUMIF

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.

==============


Re: SUMIF

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,


Re: SUMIF

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

==========


Re: SUMIF

Posted by Manu on January 23, 2002 9:18 AM
Thanks a bunck for your time Sir, that was helpful

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.