Ask Your Question
0

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,

=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 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
0

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.

=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.

edit flag offensive delete link more

Comments

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

Stats

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

Seen: 231 times

Last updated: Mar 11 '16