Countif Invalid characters

Copper Contributor
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! 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

@PedroSena 

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))

 

 

@dscheikey 

 

That was really helpful. Thanks so much