Excel Quick Tips: Count Unique Values

I’ve got an extremely short but valuable Excel tip for you today: how to count the number of unique values in a range. Sometimes you need to count values in a row or column but not if they repeat. To count only the unique numbers or words in Excel, use the following formulas depending on:

If there are no blank cells:

=SUMPRODUCT(1/COUNTIF(Range, Range))

With or without blank cells:

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

Replace Range with A1:A7 for example. 

COUNTIF is probably the function you’re most used to that will count based on a given condition. SUMPRODUCT returns the sum of the product in the range.  SUMPRODUCT functions as an array formula, you just don't have to enter it as such. 

To see how this formula works step by step, click on the cell that contains the formula, then go to Formula tab, and click Evaluate Formula and you can cycle through each step in the calculation.

For more, see the index of Excel tips page.