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" wrote in message
...


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" wrote in message
...


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.