Forum Discussion
Countif Invalid characters
[ Spoiler ] 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! e...
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.
=SUM(1/IFERROR(COUNTIF(C$12:C$165;C$12:C$165);0))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:
=COUNTA(UNIQUE(C$12:C$165))
