We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2016-03-11 22:38:24 +0200

mikeg gravatar image

updated 2020-07-28 20:57:26 +0200

Alex Kemp gravatar image

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,


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.


edit retag flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2016-03-11 23:33:32 +0200

m.a.riosv gravatar image

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




remember that FIND is case sensitive.

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

edit flag offensive delete link more


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

mikeg gravatar imagemikeg ( 2016-03-14 18:42:31 +0200 )edit

Question Tools

1 follower


Asked: 2016-03-11 22:38:24 +0200

Seen: 285 times

Last updated: Mar 11 '16