MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SumIf with more than on criteria


Posted by Rudi Holans on February 08, 2002 2:48 PM

Is it possible to use SumIf when then there is more than one criteria?
For example: sum range C1:C10 if A1="A" and B1="B".
Or need I a totally different function?

Rudi


Posted by Aladin Akyurek on February 08, 2002 2:56 PM

NO. Only when you have a between condtion like greater than 10 and less then 50.

In other cases of summing with multiple conditions, you can use an array or SUMPRODUCT formula as in:

=SUMPRODUCT((A1:A10="A")*(B1:B10="B"),(C1:C10))

=====

Posted by Brian on February 08, 2002 4:41 PM

Aladin

Aladin,

I have a similar problem using two criteria. Only I'm searching for text. i.e "employee name" and "vacation" then summing if it meets the two criteria. I've tried your formula but get an #value error message. Can you help?

Brian

Posted by Aladin Akyurek on February 09, 2002 12:49 AM

Multiconditional Sum

Brian --

Would you post the formula that you tried, along with what kind of values you have in the ranges of interest and criteria?

=========

Posted by Brian on February 09, 2002 12:41 PM

Aladin - I figured it out! Thanks!!!!

Posted by rudi holans on February 10, 2002 1:49 AM

Re: Aladin - I figured it out! Thanks!!!!

How does your formula look like Brian?

Rudi

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

Rudy

=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))))