Using =SUMPRODUCT with Multiple Criteria

**cbcrocker** · 03-10-2010, 05:31 PM

I’m having trouble with the following situation. I’m trying to use the following formula to first look in column D (see attached screen capture) for a particular company code, then look in column C for any instances of EH and then from those instances determine how many have Yes in column B.

=SUMPRODUCT( ('BE - ALL(-TDG)'!$D:$D="1100")+0,ISNUMBER(SEARCH("EH",'BE - ALL(-TDG)'!$C:$C))+0,ISNUMBER(SEARCH("Yes",'BE - ALL(-TDG)'!$B:$B))+0)

I’ve used my original formula in another workbook successfully (where I looked up a country name and then searched for the number of instances of a contract type) but with only one search:
=SUMPRODUCT( ('Africa - OPEN'!$A:$A="Libya")+0,ISNUMBER(SEARCH("Emp",'Africa - OPEN'!$F:$F))+0)

If I’m working in the wrong direction please point me down the correct path.

**NBVC** · 03-10-2010, 05:38 PM

Perhaps:

Code:

=SUMPRODUCT(('BE - ALL(-TDG)'!$D:$D="1100")*ISNUMBER(SEARCH("EH",'BE - ALL(-TDG)'!$C:$C))*ISNUMBER(SEARCH("Yes",'BE - ALL(-TDG)'!$B:$B)))

but if only those single text strings found in the cells, then perhaps only:

Code:

=SUMPRODUCT(('BE - ALL(-TDG)'!$D:$D="1100")*('BE - ALL(-TDG)'!$C:$C="EH")*'BE - ALL(-TDG)'!$B:$B="Yes")

**cbcrocker** · 03-10-2010, 05:52 PM

Your first option gave me the same results as the original formula: 0 when it should have been 155. The 2nd option gave me a #value!. Column C contains either "EH" or "COMM" depending upon contract type and column B contains either "Yes" or "No" if space allocations are utilized within the contract. This is WAY out of my comfort zone but I'll keep plugging along.

**NBVC** · 03-10-2010, 05:57 PM

How about:

Code:

=SUMPRODUCT(--('BE - ALL(-TDG)'!$D:$D="1100"),--('BE - ALL(-TDG)'!$C:$C="EH"),--('BE - ALL(-TDG)'!$B:$B="Yes"))

or

Code:

=SUMPRODUCT(--('BE - ALL(-TDG)'!$D:$D=1100),--('BE - ALL(-TDG)'!$C:$C="EH"),--('BE - ALL(-TDG)'!$B:$B="Yes"))

If still not successful, attach actual workbook sample, .xls not .jpg..

**JBeaucaire** · 03-10-2010, 05:58 PM

Also being discussed here:
http://www.mrexcel.com/forum/showthread.php?t=454048

**NBVC** · 03-10-2010, 06:03 PM

Thanks JB,

cbcrocker,

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**cbcrocker** · 03-10-2010, 06:19 PM

Figured it out with the help of Barry Houdini on the MrExcel forum. I'd inadvertantly placed quotes around the 1100. the following would work as well:

=COUNTIFS('BE - ALL(-TDG)'!$D:$D,1100,'BE - ALL(-TDG)'!$C:$C,"*EH*",'BE - ALL(-TDG)'!$B:$B,"*Yes*")

**NBVC** · 03-11-2010, 08:45 AM

Originally Posted by cbcrocker

Figured it out with the help of Barry Houdini on the MrExcel forum. I'd inadvertantly placed quotes around the 1100. the following would work as well:

=COUNTIFS('BE - ALL(-TDG)'!$D:$D,1100,'BE - ALL(-TDG)'!$C:$C,"*EH*",'BE - ALL(-TDG)'!$B:$B,"*Yes*")

That is similar to my second option in my last post.

Note that the Countifs option only works in 2007.. and if you want to share the file with 2003 users, you need Sumproduct() version.