Forum Discussion
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 ...
=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.
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))
=SUM(1/COUNTIF(AN2:AN273,AN2:AN273))
=COUNTA(UNIQUE(AN2:AN273))
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.