How to Count Unique Values in Excel

This guide will show you how you can use Excel formulas to count unique values in an Excel spreadsheet.

This guide will be useful if you want to know how many unique values there are in a range with duplicate values. We can either use Excel formulas or the built-in Filter feature.

For example, let’s assume I have the following list:

[1,3,3,2,6,6,5,6,1]

I want to know how many unique values there are. Removing duplicate values, we come up with the following list:

[1,3,2,6,5]

Removing the duplicate values gives us a total of 5 unique values. If we were to have a list of values in an Excel spreadsheet, is it possible to get the number of unique values?

Excel gives us two possible ways to count unique values in a range with duplicate values. First, we can use a filter to extract unique values. Second, we can use various Excel functions to achieve this task.

Let’s learn how to count unique values in Excel using both of these methods. In the next section, we’ll look into some real examples of spreadsheets that count unique values in a given range.

A Real Example of Counting Unique Values in Excel

Let’s take a look at a few examples of spreadsheets that count unique values in an Excel spreadsheet.

We can use Excel formulas to count the number of unique values in a given range. For example, the table below includes the names of several employees who have rendered a certain number of hours in the past week. Some names appear more than once. If we want to know how many employees rendered hours, we can use two functions together.

The UNIQUE function returns the unique values of a given range or array. Since we need to get the number, we can wrap the result with the ROWS function. The ROWS function returns the number of rows in a given range.

If you want to ignore blank cells, you may also try using the COUNTA function. The COUNTA function returns all the cells in a range that are not blank.

We can also find unique values among an entire range of numerical values. For example, the table below has a range of random values from 1 to 50. The range itself comprises 96 cells, so there is guaranteed to be some duplication in the values. How many unique values are there in the range?

Using an Excel formula, we returned a count of 41 unique values in this range. To get the number of unique values, we just have to use the following formula:

=SUMPRODUCT(1/COUNTIF(A1:H12;A1:H12))

How does this work? The COUNTIF function allows us to return a range of values that indicate how many times each value appears in the range. For example, the value 33 appears three times. We can use the SUMPRODUCT function to add up all the inverse of all these values.

The inverse of 3 is ⅓, and since it appears thrice in the list, it should add up to 1. This works for every other value in the range. This will result in the total number of unique values found in the range.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try counting unique values yourself in Excel, read our step-by-step guide in the next section.

How to Count Unique Values in Excel

We’ll now show you each step needed to start counting unique values in your Excel spreadsheets. You’ll learn how to use filters and Excel formulas to count the number of values in a range while ignoring duplicates.

Follow these  steps to start using an advanced filter to count unique values:

1. First, select the range that you would like to count. In this example, we’ve selected the range A1:A10.
   
   
2. Next, select the Advanced filter option. You can find this option under the Data tab.
   
   
3. You will see a pop-up dialog that will ask what to do with the currently selected data. Select the action “Copy to another location” and indicate another cell to copy to. Check the box for the option labeled “Unique records only” then click on OK.
   
   
4. You’ll now see that Excel has pasted a new range of values starting at the indicated target cell. The difference with these values is that they are now unique.
   
   
5. Select the new range of values and add it as an argument for the ROWS function. Hit the Enter key to evaluate the function.
   
   
6. We now know that the total number of unique values is four after filtering out duplicate values.
   
   

An issue with the method above is that it isn’t dynamic. If we add more entries to our original table, the filtered range in column D will not update. We can use Excel formulas to create a dynamic count of unique values.

1. If you want to count all unique values, including blank cells, you can use the formula =ROWS(UNIQUE(<range>)). Fill the argument with the target range.
   
2. Hit Enter to return the result. In this example, we were able to get a count of four unique names.
   
   
3. If you want to ignore blank cells, you can use the formula =COUNTA(UNIQUE(<range>)) instead.
   
4. If we were to change the first instance of “Alex” to another name, the count would dynamically update as well.
   

This guide should cover everything you need to know to start counting unique values in Excel. This step-by-step guide shows you multiple options in returning the size of a range when duplicates are removed.

Counting unique values is just one example of a clever use of Excel functions in action. Since there are so many other Excel functions out there, there is undoubtedly a solution out there for your Excel problems.

Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe