Nov 17 2022 06:12 AM
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
Nov 19 2022 03:42 AM
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))
Nov 19 2022 04:37 AM