MrExcel Publishing
Your One Stop for Excel Tips & Solutions

multi conditional sum if


Posted by Joe C on November 20, 2001 8:06 AM

Can I use the sumif on a multi conditional basis
Say I wanted to Sum colum A
If column B > 1
and Collumn was >5.
Is this possible?


Posted by Aladin Akyurek on November 20, 2001 8:16 AM

No. You need either an array formula or an inherently array function:

=SUMPRODUCT((B1:B100>1)*(C1:C100>5),(A1:A100))

will sum all values in A1:A100 if B-values are > 1 and C-values > 5.

Aladin

Posted by Joe C on November 20, 2001 8:48 AM

Thanks Again,
Thats like really cool.
I think I owe you a beer, or I have to name my next kid after you.
Your choice!

Posted by IML on November 20, 2001 9:02 AM

I think Aladin would be happy if you simply named your next kid "Amstel"

Posted by Joe C on November 20, 2001 10:26 AM

So that will make my kids names
Corona and Amstel.
Man are they gonna be messed up first time they walk into a bar.

Posted by Aladin Akyurek on November 20, 2001 10:47 AM

A virtual bottle of Amstel will also do.