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.


You can use an array formula


Use CTRL + SHIFT + ENTER when entering




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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy