0

Is there a way to count only the number of unique values in a range, say A1:A100 ?

I would prefer not to use any VBA as the workbook is already used elsewhere and is saved down not as that format.

2 Answers 2

0

You can use an array formula

=SUM(1/COUNTIF(A1:A100,A1:A100))

Use CTRL + SHIFT + ENTER when entering

2

yes:

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

This will iterate through the range and count the number of times each cell is found. That value is set as the denominator. The results are summed.

So if one value is found 3 times it would add 1/3 thre time and return 1 for it. 1/3 + 1/3 + 1/3 is 1.

You must log in to answer this question.