Countif equals and not equals not working correctly

[sunrise06]

I have a column of text values that appear to be numeric and need to remain as text values. When I use this formula =COUNTIF($BH$5:$BH$16,"00") I get the correct result, but if I use the following formula I do no, =COUNTIF($BH$5:$BH$18"<>00").

However, if the values are changed to alpha characters, it works fine - e.g., =COUNTIF($BG$5:$BG$16,"F") or =COUNTIF($BG$5:$BG$16,"<>F")

Any ideas why that is?

 

[Aladin Akyurek]

COUNTIF(S) reads text numbers and real numbers as numbers by default (as designed) and a string of 0's default also to a 0.


If you want to read text 0 or a string of 0's as text, better to switch to formulas with functions which do not have the foregoing default behavior.

 

[Rick Rothstein]

If you want to read text 0 or a string of 0's as text, better to switch to formulas with functions which do not have the foregoing default behavior.

Given the OP's data, is there any problem with using the asterisk solution I hit upon in Message #16 ?

 

[Aladin Akyurek]

Given the OP's data, is there any problem with using the asterisk solution I hit upon in Message #16 ?

[0]
=COUNTIF(A1:A7,"<>00*")

would behave the same way as

[1]
=COUNTA(A1:A7)-COUNTIF(A1:A7,"00")

[2]
=SUMPRODUCT(--(A1:A7<>"00"))

[3]
=SUM(COUNTIFS(A1:A7,{"?*","00"})*{1,-1})

The latter, i.e. [3], would exclude any non-text.

So, if the intent is counting all kind of values except 00 (or 000), [0], that is, the suggestion you made, will do. By the way, 00* means TEXT starting with 00, hence the desired effect.

 

[sunrise06]

Yes, Rick, thank you.