Countif with multiple values

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

 

[Frank Kabel]

Hi
one way: try
=SUMPRODUCT(--(E2:E84<>"GX110"),--(E2:E84<>"GX240"),--(E2:E84<>"GX260")
,--(E2:E84<>"GX270"),--(E2:E84<>""))

 

[Harlan Grove]

one way: try
=SUMPRODUCT(--(E2:E84<>"GX110"),--(E2:E84<>"GX240"),--(E2:E84<>"GX260"),
--(E2:E84<>"GX270"),--(E2:E84<>""))

...

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.

 

[Frank Kabel]

...
..

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.

Hi Harlan
definetly better!
Frank

 

[Jason Morin]

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

HTH
Jason
Atlanta, GA

 

[Bob Phillips]

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)

 

[Jason Morin]

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)




.

 

[BobT]

Thanks for all of the help. One other question. The
range changes from time to time. Can the formula pick
that up?

 

[BobT]

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

the values I am looking for if there are only 4 or 5 of
those?

 

[Frank Kabel]

Hi bob
in this case use something like
=SUMPRODUCT(--(E2:E84={"val1","val2","val3"}))

 

[Bob Phillips]

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)

 

[Aladin Akyurek]

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

 

[Harlan Grove]

Just suggest not having to deduce the cell count

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

....

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