Counting the number of unique values

You can easily count the number of unique values of the range using a simple formula.

For example, you want to count the number of unique values of the range:

Data in Excel 2016

You can enter the next formula to the range C2:C12:

=SUM(1/COUNTIF($A$2:$A$12,$A$2:$A$12))

Summarize amount from a range C2:C12:

Counting the number of unique values in Excel 2016

This formula in Excel 2016 has no limitation: If the range contains any blank cells, previous versions of Excel return an error.