Thread: COUNTIF help based one two criteria, one being a list

I would like to count the number of time "Yes" appears on a large list of names with multiple entries for each in no particular sort. I have smaller lists like the one below that group the main list into smaller teams.

Example:

A - B - C
Allen - Yes - 14
Frank - Yes - 85
David - No - 45
Bob - Yes - 12
Charlie - Yes - 13
Charlie - No - 5
Bob - No - 18
Charlie - No - 12
David - No - 42
Allen - No - 1
Edward - No - 3
Frank - Yes - 20
Bob - Yes - 812

Second sheet list:

Bob
Charlie
Frank

I would like to count the number of times "Yes" appears only for names from the smaller list. So in this example, Bob, Charlie, and Frank have 5 "Yes" entries between them. It should be noted that I am using Excel 2003. I have been trying to use SUMPRODUCT again, but can't figure out the syntax.

Thanks.

-Tom

Hello Tom,

You can use this formula

=SUMPRODUCT(ISNUMBER(MATCH(A$2:A$100,{"Bob","Charlie","Frank"},0))*(B$2:B$100="Yes"))

You can replace {"Bob","Charlie","Frank"} with a cell range containing those names e.g.

=SUMPRODUCT(ISNUMBER(MATCH(A$2:A$100,D2:D4,0))*(B$2:B$100="Yes"))