Search code examples
excelformula

Weird hidden character in Excel that counts up in COUNTIF

I have an Excel sheet which is automatically created by an export of a time scheduler system. I want to count some columns content (to filter out all rows with no content). But weird: some cells appear to be empty (in the test file C2, H2 and I2) but with =COUNTIF(D2:BZ2;"<>") (german version: =ZÄHLENWENN(D2:BZ2;"<>")) they are considered as not empty. I can't figure out what the content is and how to skip it when counted.

Here is the testfile: http://tg11.de/COUNTIF-problem.xlsx

Do You have an idea how to handle this ominously pseudo empty cell as empty with COUNTIF without running a VBA script to tidy up?

Solution

  • Please try:

    =COUNTA((FILTER(D2:BZ2,NOT(ISERROR(CODE(D2:BZ2))))))