Count rows that match 3 sets of criteria?

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.

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


"EricE" <[email protected]> wrote in message
news:[email protected]...
>
>
> 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.

"Bob Phillips" wrote:

> =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)
>
>
> "EricE" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > 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.
>
>
Perfect! Thanks Bob.

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

"EricE" wrote:

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