To count how many unique values are in a range, do you always need to use "Remove Duplicated" and then COUNT? Of course not—one formula will do.
=SUMPRODUCT(1/COUNTIF($A$2:$A$10,$A$2:$A$10))That is SUMPRODUCT(1/COUNTIF(range,range))
COUNTIF(A2:A10,A2:A10)
- It first counts how many times A2 appears in A2:A10, then how many times A3 appears, and so on up to A10...
- For example, if the "B" in A3 appears 3 times in A2:A10, the count is 3.
- Excel now has an array that records how many times each value in column A is duplicated.
1/COUNTIF(A2:A21,A2:A21))
- Take the reciprocal of the repeat counts
- For example, if "B" appears twice, that's 0.5, because it's 1/2
- The 0.5 that appears twice adds up to 1, which represents one unique value.
SUMPRODUCT(1/COUNTIF(A2:A21,A2:A21)))
- Add up all the unique values to get the answer.
[Hide]
Join our 'Office Suite Real-Time Support' one-on-one sessions to learn more about Office.
View details
