Issues with CountIf and the value of 0...

Here is my enigma...
I have cells that contain a "0". They test TRUE as Text, and FALSE as a number, yet every variation I have tried with a CountIf (or countifs) function does not test as expected. In the text posted below, the 'formula' listed in column 2 is also in Column 3 with the value returned.
I tried formatting the cells holding the values as GENERAL, TEXT, and NUMBER (noted in each column). Those cells containing TEXT do not evaluate as expected for the "Not Equal To" text of <>. Any one else have this issue and resolve it? I am using Excel in Microsoft Office Professional Plus 2010, running version 14.0.7015.1000

In EXCEL the first two sets of formulas have a value of 0 that is left justified and have the green box in the upper left corner to show they are 'text'. In the second two sets, the values are numeric values of 0.
I tried to attach an image below to show that.


CountIf with 0.jpg

Format
General Function Value Returned
0 =ISTEXT($A3) TRUE
0 =ISNUMBER($A4) FALSE
0 =COUNTIF($A5,"'=0") 1
0 =COUNTIF($A6,"<>0") 1
0 =COUNTIF($A7,"'="&"0") 1
0 =COUNTIF($A8,"<>"&0) 1
0 =COUNTIF($A9,"<>"&"0") 1
0 =LEN($A10) 1

Text Function Value Returned
0 =ISTEXT($A3) TRUE
0 =ISNUMBER($A4) FALSE
0 =COUNTIF($A5,"'=0") 1
0 =COUNTIF($A6,"<>0") 1
0 =COUNTIF($A7,"'="&"0") 1
0 =COUNTIF($A8,"<>"&0) 1
0 =COUNTIF($A9,"<>"&"0") 1
0 =LEN($A10) 1

General Function Value Returned
0 =ISTEXT($A3) FALSE
0 =ISNUMBER($A4) TRUE
0 =COUNTIF($A5,"'=0") 1
0 =COUNTIF($A6,"<>0") 0
0 =COUNTIF($A7,"'="&"0") 1
0 =COUNTIF($A8,"<>"&0) 0
0 =COUNTIF($A9,"<>"&"0") 0
0 =LEN($A10) 1

Number Function Value Returned
0 =ISTEXT($A3) FALSE
0 =ISNUMBER($A4) TRUE
0 =COUNTIF($A5,"'=0") 1
0 =COUNTIF($A6,"<>0") 0
0 =COUNTIF($A7,"'="&"0") 1
0 =COUNTIF($A8,"<>"&0) 0
0 =COUNTIF($A9,"<>"&"0") 0
0 =LEN($A10) 1

Originally Posted by tfpearce

I have cells that contain a "0". They test TRUE as Text, and FALSE as a number, yet every variation I have tried with a CountIf (or countifs) function does not test as expected.

Hi

Like Biff said, COUNTIF() will evaluate TEXT numbers and numeric numbers as being equal.

If you use COUNTIFS(), however, you can add the test to count just text or number values

For ex., to count "0" (text value)

=COUNTIFS(A:A,0,A;A,"*")

and to count 0 (number value)

=COUNTIFS(A:A,0,A;A,"<>*")