Countif with multiple values

  • Thread starter Thread starter BobT
  • Start date Start date
B

BobT

Excel 2000. Am trying to get a count of the number of
cells in a range that do not contain certain values. Am
assuming I need to use the countif function. Range and
variables I am using are:
Range E2:E84
I want to count cells that do not contain values GX110,
GX240, GX260, GX270. Do I need an if statement along with
a <> for each value? I don't seem to find much in help
for a situation with multiple values.
Thanks.
 
Hi
one way: try
=SUMPRODUCT(--(E2:E84<>"GX110"),--(E2:E84<>"GX240"),--(E2:E84<>"GX260")
,--(E2:E84<>"GX270"),--(E2:E84<>""))
 
one way: try
=SUMPRODUCT(--(E2:E84<>"GX110"),--(E2:E84<>"GX240"),--(E2:E84<>"GX260"),
--(E2:E84<>"GX270"),--(E2:E84<>""))
Click to expand...
...

Another way,

=SUMPRODUCT((E2:E84<>"")-ISNUMBER(MATCH(E2:E84,
{"GX110","GX240","GX260","GX270"},0)))

which allows the set of strings to exclude from the count to be changed much
more easily.
 
Harlan said:
...
..

Another way,

=SUMPRODUCT((E2:E84<>"")-ISNUMBER(MATCH(E2:E84,
{"GX110","GX240","GX260","GX270"},0)))

which allows the set of strings to exclude from the count to be
changed much more easily.
Click to expand...

Hi Harlan
definetly better!
Frank
 
Jason,

Just suggest not having to deduce the cell count

=ROWS(E2:E84)-SUM(COUNTIF(E2:E84,"GX"&{110,240,260,270}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob. Yeah, you're right. Just got lazy.

Jason
-----Original Message-----
Jason,

Just suggest not having to deduce the cell count

=ROWS(E2:E84)-SUM(COUNTIF(E2:E84,"GX"&{110,240,260,270}))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Thanks for all of the help. One other question. The
range changes from time to time. Can the formula pick
that up?
 
-----Original Message-----
Excel 2000. Am trying to get a count of the number of
cells in a range that do not contain certain values. Am
assuming I need to use the countif function. Range and
variables I am using are:
Range E2:E84
I want to count cells that do not contain values GX110,
GX240, GX260, GX270. Do I need an if statement along with
a <> for each value? I don't seem to find much in help
for a situation with multiple values.
Thanks.
.
One last comment. Would I be better off searching for
Click to expand...
the values I am looking for if there are only 4 or 5 of
those?
 
Hi bob
in this case use something like
=SUMPRODUCT(--(E2:E84={"val1","val2","val3"}))
 
Surely, that would depend upon the requirement, whether how many match or
how many don't match.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If the size of the complement of the set GX110, GX240, GX260, GX270 is >= 3
then:

=SUMPRODUCT(--ISNUMBER(MATCH(E2:E84,{"x","y","z"},0)))

If {"x","y","z"}is in say G2:I2 or G2:G4, the above formula becomes:

=SUMPRODUCT(--ISNUMBER(MATCH(E2:E84,G2:G4,0)))

If the size of the complement of the set GX110, GX240, GX260, GX270 is = 2
then:

=SUMPRODUCT((E2:E84="x")+(E2:E84="y"))

If the size of the complement of the set GX110, GX240, GX260, GX270 is = 1
then:

=COUNTIF(E2:E84,"x")
 
Bob Phillips said:
Just suggest not having to deduce the cell count

=ROWS(E2:E84)-SUM(COUNTIF(E2:E84,"GX"&{110,240,260,270}))
Click to expand...
....

FWIW, this would count blank cells in E2:E84.
 
Back
Top