Count rows that match 3 sets of criteria?

E

EricE

How do I count all the rows that match three sets of criteria? For example:
A B C
OM 1 1 1
OM2 3 6
BC 4 4
OM2 1 3
RR 2 2
BC 4 1

Say I want to count how many time OM 1 and OM2 appear with either a 1 or 4
in column B and when column C has a number that is between 1 and 5.

So far I can count all the times OM 1 and OM2 appear with a 1 and 4 in
column C by using this: =SUMPRODUCT(--ISNUMBER(MATCH(A1:A6,{"OM 1","OM
2"},0)),--ISNUMBER(MATCH(B1:B6,{1,4},0))). I was thinking that I could just
add something like --(C1:C6 >=1),(C1:C6<=5) but that gives me a #value error.
I am also entering the formula with crtl-shift-enter. Can this even be done?
I imagine it can. Thanks in advance for any help anyone can provide.
 
B

Bob Phillips

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A6,{"OM 1","OM
2"},0)),--ISNUMBER(MATCH(B1:B6,{1,4},0)),--(C1:C6 >=1),--(C1:C6<=5))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
E

EricE

Bob Phillips said:
=SUMPRODUCT(--ISNUMBER(MATCH(A1:A6,{"OM 1","OM
2"},0)),--ISNUMBER(MATCH(B1:B6,{1,4},0)),--(C1:C6 >=1),--(C1:C6<=5))

--

HTH

RP
(remove nothere from the email address if mailing direct)
Perfect! Thanks Bob.
 
S

Sloth

=SUMPRODUCT((A1:A6="OM
1")+(A1:A6="OM2"),(B1:B6=1)+(B1:B6=4),(C1:C6>=1)+(C1:C6<=5)-1)

The first is either one, the other or neither so it results in a 1, 1 or 0.
The last one is either one or both so it results in a 1 or 2 before the
subtraction of 1. And since they all include a mathematical operator the --
is not needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top