Countif doesn't work

  • Thread starter Thread starter Reggie
  • Start date Start date
R

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
 
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
 
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
 
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*")
 
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
 
Hi Reggie

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

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




.
Click to expand...
 
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



.
Click to expand...
 
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 ?
 
JulieD said:
you should really be thanking Frank :)
Click to expand...

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
 
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 :-)
 
Frank Kabel said:
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 :-)
Click to expand...

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..
Click to expand...
... which influenced my initial thoughts as expressed to him

Cheers, Frank !
 
Max said:
Now try in C1:

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

C1 returns 3, the correct answer ?
Click to expand...

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.
 
Harlan Grove said:
.. 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.
Click to expand...

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>
 
Max said:
Less need for one to hazard guesses then <g>
Click to expand...
....

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.
 
Back
Top