MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sumif with two conditions.


Posted by Noel on August 30, 2001 7:06 AM

Hi All,

I have encountered the following problem:
I have three columns, A, B & C. Col A contains a list of names, Col b contains a list of countries and Col C contains a list of Job types. I want, for example, to count the number of Engineers in Ireland with the following formula :

=SUM(IF(B10:B20="Ireland",IF(C10:C18="Engineering",1,0),0))


But it doesn't seem to work.

Help would be greatly appreciated.

- N


Posted by Mark W. on August 30, 2001 7:16 AM

{=SUM((B10:B20="Ireland")*(C10:C18="Engineering"))}

Posted by Aladin Akyurek on August 30, 2001 7:26 AM

Noel,

It can be done using SUMPRODUCT:

=SUMPRODUCT((B2:B20="ireland")*(C2:C20="engineer")*(LEN(A2:A20)>0))

Another possibilty would be using PivotTables, I guess.

Aladin

========

Posted by Noel on August 30, 2001 7:30 AM

Thanks Mark. Much appreciated.

Posted by Aladin Akyurek on August 30, 2001 7:34 AM

By the way,


=SUMPRODUCT((B2:B20="ireland")*(C2:C20="engineer"))

will also work.

========== Noel, It can be done using SUMPRODUCT: =SUMPRODUCT((B2:B20="ireland")*(C2:C20="engineer")*(LEN(A2:A20)>0)) Another possibilty would be using PivotTables, I guess. Aladin ======== : Hi All, : I have encountered the following problem: