Excel Countif 0, 00, 000 separate entries

Question

I want to count 0, 00 and 000 in my data which is formatted as TEXT but the countif() formula gives me same value for each of the three search criteria.

Please find the attached image for more details

Answer 1

The problem occurs because Excel will evaluate each cell in the column A with the expressions "=000", "=00" and "=0", witch are obviously the same (zeros inside the expressions are not quoted, therefore are numbers), although not what we would expect.

You can use my Excel's favorite Swiss knife, the SUMPRODUCT:

=SUMPRODUCT(--(A$2:A$12=B2))

The double minus sign will force the boolean expression into 1 (true) or 0 (false).

Answer 2

Strange, it seems you can't prevent Excel converting text to numbers when possible.

Here is a workaround with an array formula (need to enter using CTRL+SHIFT+ENTER):

=SUM(IF($A$2:$A$12=B2,1,0))

Answer 3

Forget all the VBA and over complicated sum statements the LEN function in excel counts the character string. If you have leading or trailing spaces use the TRIM function first but it's probably as simple as =LEN(A2).

To work around anything other than a string of zeros I would use =IFERROR(IF(B1*"1"=0,LEN(B1),0),0) the IFERROR handles that "X" and by multiplying the text string of zeros by "1" you convert it to a number so the IF statement will equate it to "0" and run the "value if true" statement LEN(B1)