Count how many times a set of criteria are met in an array of cells

[mikeg]

I’m trying to write a formula that will count up the number of instances a set of criteria are met in an array of cells

So far I have been using the SUMPRODUCT() function but it doesn’t allow me to use multiple criteria for matching. For example,

=SUMPRODUCT(A1:A100=“MATCH1”,B1:B100=“MATCH2”)

But what I really want is something like,

=SUMPRODUCT(A1:A100=“MATCH1”,B1:B100=“MATCH2a” || “MATCH2b”)

Where the second criteria is cells that match either string “MATCH2a” OR “MATCH2b”.

Any help would be appreciated.

Mike

[mariosv]

As the result of a comparision is 1 for True and 0 for False, it can be used in operations.

=SUMPRODUCT(A1:A100="MATCH1",((B1:B100="MATCH2a")+(B1:B100="MATCH2b"))>0) 

or

=SUMPRODUCT(A1:A100="MATCH1",NOT(ISERROR(FIND(B1:B100," MATCH2a MATCH2b")))) 

remember that FIND is case sensitive.

or with COUNTIFS() if it is in your version.

[mikeg]

Thanks, that not only helps with this problem, but provides me more general insight into using these functions.