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?

 

[AlphaFrog]

Try this...

=COUNTIF($BH$5:$BH$16,"=??")
=COUNTIF($BH$5:$BH$16,"<>??")

 

[sunrise06]

That does not seem to work. For the "<>??" I get 0 and the result should be 2.

 

[AlphaFrog]

It worked for me. Maybe something about your data I may not appreciate. As I understand your description, the data is text-numbers.

Alternatively, If the equals criteria formula works and you have a fixed range you can calculate the <> criteria...

=ROWS($BH$5:$BH$16) - COUNTIF($BH$5:$BH$16,"=??")

 

[Rick Rothstein]

...but if I use the following formula I do not, =COUNTIF($BH$5:$BH$18,"<>00").

You are missing the comma that is supposed to separate the range from the condition (see what I added in red above).

 

[jtakw]

Hi,

I can confirm what OP is describing, thou I don't know why Excel is treating the Text 00 differently when tested within the COUNTIF:

Book1
	A	B	C	D	E	F
1	00	4	7	TRUE	FALSE	3
2	10			FALSE	TRUE
3	00			TRUE	FALSE
4	20			FALSE	TRUE
5	11			FALSE	TRUE
6	00			TRUE	FALSE
7	00			TRUE	FALSE
Sheet338

Cell Formulas
Range	Formula
B1	=COUNTIF(A1:A7,"00")
C1	=COUNTIF(A1:A7,"<>00")
D1	=A1="00"
E1	=A1<>"00"
F1	=COUNTA(A1:A7)-COUNTIF(A1:A7,"00")

D and E formulas copied down.
Looks like we can work around it by using something like F1 formula.

 

[sunrise06]

Well, yes I am and I caught that after posting. Still does not correct the problem. Thanks

 

[sunrise06]

Yes, jtakw, the F1 formula is my work around it but I don't like it as it is apart of a much larger formula.

Thanks!

 

[jtakw]

Well, here's another "workaround":

Book1
	A	F	G
1	00	3	3
2	10
3	00
4	20
5	11
6	00
7	00
Sheet338

Cell Formulas
Range	Formula
F1	=COUNTA(A1:A7)-COUNTIF(A1:A7,"00")
G1	=SUMPRODUCT(--(A1:A7<>"00"))

 

[Fluff]

Another option
=SUMPRODUCT(--(A1:A7<>"00"))

beaten2it