Countif Invalid characters

Copper Contributor

Hello everyone,


Can anyone tell me how to count distinct text (SUM(1/COUNTIF(C$12:C$165;C$12:C$165)) to a column with several different "invalid" characters without getting #value! error?

I could not copy&paste these characters to this screen so I will try to describe them as "upside down question mark", "question mark with square around", etc.


Would appreciate any support, thanks

2 Replies


I only get a #DIV/0! error if a space appears in the list. Then it tries to divide by zero, which leads to this error.


This formula catches the error. Actually, the #VALUE! Error should also be ignored.


In Excel 2021 for Mac or Excel for Microsoft 365 for Mac, this formula should also work:






That was really helpful. Thanks so much