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))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.
- No, all my values are there, all are words, no zeros or blanks. I am not using Excel 365 is that the issue?
