Counting the number of unique values

Excel 2016 2013 2010 2007 2003
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 365

You can use the next formula:

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

Counting the number of unique values in Excel 365

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

See also this tip in French: Compter le nombre de valeurs uniques.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.

How to count days of week in the range of dates

How to count days of week in the range of dates

From time to time, you need to count days of week in some date range or between two dates (see How to count days of week between two dates). Excel proposes different formulas to work with data. It is easy to create the formula you need for this task:
How to count days of week between two dates

How to count days of week between two dates

From time to time, you need to count days of week between two dates or in some date range (see How to count days of week in the range of dates). Excel proposes different formulas to work with data. It is easy to create the formula you need for this task:
Unique items in the list

Unique items in the list

To get unique items from a range, you can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location.