Count Unique Values in Excel: A Complete Guide

As a regular Excel use, you should learn how to Count Unique Values in Excel. Because when you need to identify unique customers, products or sales, counting unique values make the work for you. Here below, you can find everything about counting unique values in Excel.

1. Understanding excel count unique values

Before getting into formula steps, we should learn what we mean by unique values in Excel. A unique value refers to a single instance of a particular value in data. For example, if we have a list of customers, each customer’s name should appear only once in the list. So, each name will be unique.

2. excel count distinct values with the COUNTIF Function

The COUNTIF function is one of the simplest way. And also, it is the most common way to count unique values in Excel. The COUNTIF function counts the number of cells in a range. And these cells meet specific criteria. If you wanna the COUNTIF function to count unique values, we need to specify cells and the criteria for counting those values.

How to use countif for multiple criteria?

=COUNTIF(A2:A10,"<>"&"")+1

This formula counts the number of unique values in cells A2 to A10. The “<>”&”” part of the formula specifies we are looking for non-blank cells. The +1 at the end of the formula is added to account for the header row tough.

3. Counting Unique Values with the SUMPRODUCT Function

The SUMPRODUCT function is another simple way. The SUMPRODUCT function multiplies two or more arrays. And then, it adds up the results of those multiplications.

When we use the SUMPRODUCT function to count unique values, we are multiplying two arrays. Here, one of them contains a list of unique values. And another one has the count of how many times each value appears.

sum unique values excel

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

This formula counts  unique values in cells A2 to A10. The COUNTIF function generates an array showing how many times each value appears in our data. We then use 1/ that count to generate an array of unique values. And the SUMPRODUCT function adds up the results of those multiplications.

4. excel count of unique values with PivotTables

PivotTables are usually ideal for analyzing and summarizing large data. One way of using PivotTables is to count unique values. If you wanna use a PivotTable to count unique values, we need to create a PivotTable with our data. And then, we should drag the column containing the values we want to count to the Values field.

1. First, we should select the data range.
2. You can click Insert in the top menu.
3. Now, you should create your PivotTable.
4. In the Create PivotTable box, you will select the location of PivotTable. And then, you can click OK.
5. Now, you will drag the column we want for counting to Values field.
6. In the Value Field Settings dialog box, you can now select Count as the function.

The PivotTable will now display the number of unique values in that range.

5. Using Advanced Filters to Count Unique Values

Excel’s Advanced Filter is good for filtering and sorting tables and data. It can also work for counting unique values. To use the Advanced Filter to count unique values, we need to create a new list first. And it should contain only unique values.

1. Now, you can start with selecting data range.
2. Then, you can click Data at top.
3. You should click Advanced option in Sort & Filter group.
4. In the Advanced Filter menu, you can select Copy to another location.
5. In Copy to field, you should select a new location for these unique values.
6. You can now check Unique records only checkbox and click ok.

The new list will now contain only the unique values from your data. We can count the number of unique values using any methods above then.

6. Using Excel Add-Ins to Count Unique Values

Excel also comes with variety of add-ins to perform specific tasks. Good news is, this is including counting unique values. One of the most popular add-ins for counting unique values is Duplicate Remover add-in. If you wish to use the Duplicate Remover add-in to count unique values, we need to install and activate the add-in first.

1. First is clicking File menu at top and then, selection Options.
2. Now, you should go for Add-Ins.
3. In the Manage field, you will select Excel Add-Ins and click Go.
4. Then, you should Check the Duplicate Remover checkbox.
5. Now you select the data range.
6. Last thing is to select Remove Duplicates in Data Tools group.

The Duplicate Remover add-in will remove all duplicate values. Hence, it will be leaving only the unique ones.

7. COUNTIF between two values and more

• Here, you should star to Remove duplicates from your data before countin.
• You should use named ranges to make your formulas easier for excel count unique values.
• It is better to use the IF function to add criteria to your counting.
• Also, you can use conditional formatting to highlight unique values.
• But each method comes with limits. So, you should choose the one that best fits to your table.

8. Common Errors for excel count unique values with criteria

• You should have your data in the correct format (text, numbers, dates, etc.) before using any of these methods.
• And do not forget to check your formulas for errors. Such as missing parentheses or quotation marks.
• Also, range references should be correct and do not include any extra data.
• If you are using a PivotTable, you should first organize data and label your columns.

9. Conclusion on how to count unique values in excel

Counting unique values is easy actually when working with data in Excel. Here tough, we covered several methods for counting unique values. These are also including the COUNTIF and SUMPRODUCT functions. As well as basic ones like PivotTables, Advanced Filters and Excel Add-Ins.

Now, you can read How to Control Charts in Excel: A Comprehensive Guide for details on Excel charts and more. You can also check the other content.