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

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

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-07-28 20:57:36.378624

Sort by » oldest newest most voted

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.

more