# 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