Issues with countif and values of 0

[tfpearce]

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.



[TABLE="width: 336"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Format[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]General[/TD]
[TD]Function[/TD]
[TD]Value Returned[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=ISTEXT($A3)[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=ISNUMBER($A4)[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A5,"'=0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A6,"<>0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A7,"'="&"0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A8,"<>"&0)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A9,"<>"&"0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=LEN($A10)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Text[/TD]
[TD]Function[/TD]
[TD]Value Returned[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=ISTEXT($A3)[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=ISNUMBER($A4)[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A5,"'=0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A6,"<>0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A7,"'="&"0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A8,"<>"&0)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=COUNTIF($A9,"<>"&"0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]=LEN($A10)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]General[/TD]
[TD]Function[/TD]
[TD]Value Returned[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=ISTEXT($A3)[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=ISNUMBER($A4)[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A5,"'=0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A6,"<>0")[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A7,"'="&"0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A8,"<>"&0)[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A9,"<>"&"0")[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=LEN($A10)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD]Function[/TD]
[TD]Value Returned[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=ISTEXT($A3)[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=ISNUMBER($A4)[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A5,"'=0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A6,"<>0")[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A7,"'="&"0")[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A8,"<>"&0)[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=COUNTIF($A9,"<>"&"0")[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]=LEN($A10)[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Was this data imported from somewhere? If so, did you change the cell format to number then double click/press F2/click on the formula window and enter for some of the cells but not all?

Note that putting an apostrophe in front of the zero won't exclude it from countif:

Excel 2010
	A
1	0
2	0
3	2
Sheet8

Cell Formulas
Range	Formula
A3	=COUNTIF(A1:A2,0)

One way to discern numerical zeroes from text:

Excel 2010
	A	B
1	0
2	0	A2 has apostrophe
3	1
4	0
5
6	3
7
8	2
Sheet8

Cell Formulas
Range	Formula
A6	=COUNTIF(A1:A4,0)
A8	=SUMPRODUCT(ISNUMBER(A1:A4)*(A1:A4=0))