Forum Discussion

PedroSena's avatar
PedroSena
Copper Contributor
Nov 17, 2022

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

Excel on Mac

2 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    Nov 19, 2022

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