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

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

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.

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