0

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?

Improve this question
6
  • 1
    What's the length of such a cell? Do you know ASCII values? (Or isn't it ASCII?) ...
    – Dominique
    Commented May 17 at 13:40
  • 1
    Check the contents of the cells by writing =code([reference the cell[) across. You may have hidden characters.
    – bdpolinsky
    Commented May 17 at 13:42
  • =LENGTH(D2)is shown as 0. =CODE(D2)returns an error. 🤷🏼‍♂️
    – Robbit
    Commented May 17 at 13:43
  • 1
    What about this thread? Any help?stackoverflow.com/questions/32617535/…
    – bdpolinsky
    Commented May 17 at 13:44
  • 1
    Maybe make use of the thrown error, like this: =COUNTA((FILTER(D2:BZ2,NOT(ISERROR(CODE(D2:BZ2))))))
    – bugdrown
    Commented May 17 at 15:21

1 Answer 1

Reset to default
1

Please try:

=COUNTA((FILTER(D2:BZ2,NOT(ISERROR(CODE(D2:BZ2))))))
Improve this answer
1
  • Thank You so much. The idea is good. 👍🏼 However the formula has to be completed: The empty filter result is counted as 1 (even if it is replaced with "". Here is a functioning workaround: =IF(FILTER(D2:BZ2, NOT(ISERROR(CODE(D2:BZ2))), "x-x")="x-x", 0, COUNTA (FILTER(D2:BZ2, NOT(ISERROR(CODE(D2:BZ2))))))
    – Robbit
    Commented May 24 at 16:00

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