I want to count
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
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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
The double minus sign will force the boolean expression into 1 (true) or 0 (false).
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)