Forum Discussion

asd787's avatar
asd787
Copper Contributor
Jan 13, 2023

Counting unique words while counting duplicates as one

Hi,

 

What formula can I use to count unique values while counting duplicates as one. For example:

 

apple, apple, apple, banana, banana, orange, plum

 

should produce a number of 4.

 

Thank you,

excel
Formulas and Functions

7 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 13, 2023

    asd787 

    =COUNTA(UNIQUE(F3:F9))

     

    With Office 365 or Excel for the web you can try this formula.

    =SUM(1/COUNTIF(F3:F9,F3:F9))

    Without Office 365 or Excel 2021 you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

    • asd787's avatar
      asd787
      Copper Contributor
      Jan 13, 2023
      Hi, thank you for your reply. Could you please copy and past the exact formula you used for the cell because I tried the below and I'm getting an error message for the first one and a value of 1 for the second. Thank you!
      =SUM(1/COUNTIF(AN2:AN273,AN2:AN273))
      =COUNTA(UNIQUE(AN2:AN273))
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jan 13, 2023

        asd787 

        You are welcome. I assume that range AN2:AN273 has only empty cells.

        =COUNTA(UNIQUE(AN2:AN273))

        This formula returns 1 if there are only blanks because the result of =UNIQUE(AN2:AN273) is 0 and the COUNTA of 0 is 1.

        =SUM(1/COUNTIF(AN2:AN273,AN2:AN273))

        This formula returns an error if there are only blanks because it's not possible to divide by 0.

         

Resources