MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Rudi Holans

Posted by Brian on February 10, 2002 8:12 AM

=SUM(IF($G$1:$G$99="employee name",IF($I$1:$I$99="vac",($H$1:$H$99))))+SUM(IF($B$1:$B$99="employee name",IF($D$1:$D$99="vac",($C$1:$C$99))))

Posted by Aladin Akyurek on February 10, 2002 10:22 AM


Aloha Brian,

I believe you mean by employee name a name of interest like Brian or John, while "vac" is a value that qualifies numbers in $C$1:$C$99 and $H$1:$H$99. Moreover, your formula must be array-entered.

It's indeed an example of multiconditional summing applied to two different areas with the same layout. Moreover, it's equivalent to:

=SUMPRODUCT(($B$1:$B$99="brian")*($D$1:$D$99="vac"),$C$1:$C$99)+SUMPRODUCT(($G$1:$G$99 ="brian")*($I$1:$I$99 ="vac"),$H$1:$H$99)

PS. It appears that I failed to reply to you about that SUMIF glitch. Mea Culpa. I hope to come back to you on that.


Posted by Brian on February 10, 2002 11:45 AM



Much mahalo. I'll use yours, it's much shorter.
And here I was so excited to have figured something out. Thanks again for all your help and time you spend on it!