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 ...
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.
- No, all my values are there, all are words, no zeros or blanks. I am not using Excel 365 is that the issue?
- this formula worked for me in the end: =SUMPRODUCT(1/COUNTIF(A2:A273,A2:A273))
The UNIQUE function works only with Office 365 or Excel 2021 or Excel for the web.
=SUM(1/COUNTIF(AN3:AN273,AN3:AN273))This formula works in older versions e.g. in Excel 2013 if you enter it with ctrl+shift+enter. I've attached a sample file where you can see the result.