Count rows that match 3 sets of criteria?
"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.
|