Countif doesn't work

[Reggie]

Hello,

A simple =COUNTIF(Errorstep,"*201*") will simply not
work. What could possibly be wrong? Is my machine
broken? Am I an idiot? (No answer requested for the
latter!)

I've changed the format of the range cells from numbers to
text and back to numbers. I give up! Are there any
suggestions out there?

Your assistance is greatly appreciated!

Reg

 

[Vasant Nanavati]

It might help if you could elaborate on "doesn't work" .

 

[Max]

Why not just use: =COUNTIF(ErrorStep,201)

 

[Reggie]

Because the "201" may sometimes have other data in the
same cell.

 

[JulieD]

Hi Reggie

do they actually need to be "numbers" or can they be text ... as
=COUNTIF(ErrorStep,"*201*")
will work if they're formatted as text

Cheers
JulieD

 

[Reggie]

Thanks for answering!

My range is correct, my syntax, as far as I can see, is
correct, but the result is lower by far than what I know
the range contains.

I need the asterisks in there because sometimes the "201"
cell has other data in it - could be on either side of
the "201". But you know, for the life of me, it looks
like when you use the asterisk it only picks up on "201"
cells with the extra data in it. This isn't how that is
supposed to work, is it?

Reg

 

[Frank Kabel]

Hi
this formula won't pickup numeric data. That is a cell containing only
'201' as numeric won't be counted. Sou you may try
=COUNTIF(A:A,201) + COUNTIF(A:A,"*201*")

 

[Reggie]

Hi Julie,

Thanks for helping. Actually, I did format the entire
column as text. Would you believe that if I use the
formula as you've written below it ONLY counts the 201
cells with the extra data and not the rest. (The vast
majority of the column is just "201".)

Wierd, huh?!?

Ciao, Reg

 

[JulieD]

Hi Reggie

then Frank's suggestion will work best for you
=COUNTIF(A:A,201) + COUNTIF(A:A,"*201*")

Regards
JulieD

 

[JulieD]

you should really be thanking Frank

Thank you very much, Ms. Julie!
Reg

 

[Reggie]

Thank you very much, Ms. Julie!
Reg

-----Original Message-----
Hi Reggie

then Frank's suggestion will work best for you
=COUNTIF(A:A,201) + COUNTIF(A:A,"*201*")

Regards
JulieD




.

 

[Reggie]

Vielen Dank!
Reg

-----Original Message-----
Hi
this formula won't pickup numeric data. That is a cell containing only
'201' as numeric won't be counted. Sou you may try
=COUNTIF(A:A,201) + COUNTIF(A:A,"*201*")

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Max]

hmm .. I'm not so sure ..

Try this simple experiment ..

Enter in A1:A3

text 201
201
'201

Name the range A1:A3 as : Errorstep

Put in B1:

=COUNTIF(Errorstep,201)+COUNTIF(Errorstep,"*201*")

Above is the translation of Frank's suggestion:
B1 returns 4 >> wrong result ??

Now try in C1:

=COUNTIF(Errorstep,"*201*")+SUMPRODUCT(ISNUMBER(Errorstep)*(Errorstep=201))

C1 returns 3, the correct answer ?

 

[Max]

you should really be thanking Frank

Totally agree that Frank deserves a lot of thanks for his
many, many contributions (he hardly sleeps <bg>) ..

But .. in this instance I'm not sure that Frank's suggestion
would give the correct results

Perhaps you might want to refer my post
in the other branch of the thread

 

[Frank Kabel]

Hi Max
I agree with you that in this case (depending on the OP's data) the
addition of two SUMIFs could lead to a wrong result. Best would be the
OP stores all his data as 'Text' and then uses a simple COUNTIF

 

[Max]

Hi Max
I agree with you that in this case (depending on the OP's data) the
addition of two COUNTIFs [typo corrected] could lead to a
wrong result. Best would be the OP stores all his data as 'Text'
and then uses a simple COUNTIF

And I guess I was "misled" by the OP's lines in his original post:

I've changed the format of the range cells
from numbers to text and back to numbers..

... which influenced my initial thoughts as expressed to him

Cheers, Frank !

 

[Harlan Grove]

Now try in C1:

=COUNTIF(Errorstep,"*201*")+SUMPRODUCT(ISNUMBER(Errorstep)
*(Errorstep=201))

C1 returns 3, the correct answer ?

If this works, then there's a simpler way to express it.

=SUMPRODUCT(--(SUBSTITUTE(Errorstep,"201","")<>Errorstep))

Also this begs the question whether a cell containing the *text* 5201 or
2016 should count as a 201 match, or a cell containing the number 123.201
should. The latter may be an overly artificial situation, but the former
could arise.

 

[Max]

.. If this works, then there's a simpler way to express it.
=SUMPRODUCT(--(SUBSTITUTE(Errorstep,"201","")<>Errorstep))
.. Also this begs the question whether a cell containing
the *text* 5201 or 2016 should count as a 201 match,
or a cell containing the number 123.201 should.
The latter may be an overly artificial situation,
but the former could arise.

Thanks for the refinement and insights, Harlan !

Probably it'd have been useful/easier if the OP had posted
some examples of the different data present/expected in Errorstep
that he wants to use COUNTIF on ..

Less need for one to hazard guesses then <g>

 

[Harlan Grove]

Less need for one to hazard guesses then <g>

....

As few OPs ever provide full details until the 4th or 5th level of
follow-up, guessing is an unavoidable part of responding. As is asking
leading questions.