Count Unique Values in Excel

Excel Functions Tutorials

Table of Contents

Count Unique Values in Excel
Using SUM and COUNTIF Function
Using SUMPRODUCT and COUNTIF Function
Handling Blanks in the Range

Usually, we work with data and that data includes multiple values repeating at the same time. Often we need to count only unique values in excel by ignoring all the duplicate appearances.

When we get the count of unique values in excel we actual arrives at the total single entry values in the list.

To count only unique values in excel, we can adopt several options and the top ones are shown below.

Count unique values in excel using Sum and Countif function.
Count unique values in excel using SUMPRODUCT and Countif function.
Count unique values in excel using an advanced filter.

In this article, I will discuss “Count Unique Values in Excel using the Countif Function”

#1 – Count Unique Values in Excel using SUM and COUNTIF Function

Assume you are working as a sales manager and you have sales data in front of you. Many customers purchased the product at multiple time frame in the month. You need total customers in the month.

In this example, I will discuss the method of counting unique values using the SUM function with the COUNTIF function.

Step 1: Copy the below data to your excel file.

Step 2: Apply the below formula in the cell E3 to get the total unique values.

Now let me break down the formula in detail.

The formula I have used here is:

If take a close at the formula it is surrounded by curly brackets. It not entered by me rather it is an indication that it is an array formula.

Once you finished with the formula before we hit enter we need to use key:

Ctrl + Shift + Enter this would enter those curly brackets automatically for you.

This is a bit new formula if you are working with array formulas for the first time. Let me break down the formula into three pieces.

Firstly I will explain the COUNTIF formula. Select the countif formula part and press F9.

Now press the key F9.

Now we are dividing the above values by the number 1.

Now Sum function add all those numbers that are appeared in the above image and gives the total as 12. Therefore the total number of unique values in the list is 12.

How Does Calculation work?

If the values are there is the list 2 times then it is ½ i.e. 0.5. If the value is there in the list 3 times then it is 1/3 i.e. 0.3333.
In our list, the first name is Ruturaj that is appearing 3 times in the list so our result showing 0.33333333 as the value.
Our second name Kamal appearing only one time and the formula read like this 1/1 i.e.1 only.
Like this COUNTIF and SUM function can give us the total number of unique values in the list.

#2 – Count Unique Values in Excel using SUMPRODUCT and COUNTIF Function

Take the same data from the previous example.

In this example, I will discuss the method of counting unique values using SUMPRODUCT function with COUNTIF function. This is not an array formula I am going to use in this example rather a normal formula I am using.

Step 1: Copy the below data to your excel file.

Step 2: Apply the below formula in the cell E6 to get the total unique values.

Now let me break down the formula in detail.

The formula I have used here is:

In our previous example, I have used Array formula i.e. closing the formula with Ctrl + Shift + Enter.

If I break down the formula using F9 key it exactly works the same as the previous one.

Now SUMPRODUCT function adds all those numbers that are appeared in the above image and gives the total as 12. Therefore the total number of unique values in the list is 12.

Handling Blanks in the Range

If the value list contains any of the empty cells then the formula returns the result as an error in excel i.e. #DIV/0!.

In the above image row, number 10 is the empty row. Since there is an empty row the formula returned the result as an error i.e. #DIV/0!.

We can handle these kinds of errors by inserting nothing ( “” ) value to it.

At the end of the countif formula, we need to nothing value by using ampersand symbol then deduct the whole result by -1 because even the empty cell is treated as a unique value by the formula.

Note: If there are two empty rows then we can use -2, if there are 3 empty rows then we can use -3 and so on.

Things to Remember Count Unique Values in Excel

We need to use Ctrl + Shift + Enter to close the formula in case of array formulas. Even when we are editing the formula we cannot simply close we need to Ctrl + Shift + Enter.
We can get the unique list just by removing duplicate values form the list.
An array formula cannot be applied to merged cells.

You can download this Count Unique Values Excel template here – COUNT Unique Values Excel Template

Count Unique Values in Excel