5

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

enter image description here

  • Have you considered using a programming language instead? – Aaron Hall Oct 21 '15 at 12:04
4

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).

| improve this answer | |
1

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))

enter image description here

| improve this answer | |
1

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.