Count Unique Values In Excel

What Is Counting Unique Values In Excel?

The different techniques to count unique values in Excel enable us to determine the total distinct data values in a given range.

Users can count unique values in Excel in various data formats while reviewing financial and statistical data.

For example, the table below lists fruits and their grades.

Count Unique Values in Excel Example - 1

And the requirement is to count the distinct fruits in column A and display the output in cell A15.

Then, we can count unique values in Excel column A using the Excel COUNTIF function within the Excel SUM function in the target cell.

Count Unique Values in Excel Example - 2

In the above count unique values in Excel column example, we apply the SUM()-based formula, containing the COUNTIF(), as an array formula.

First, the COUNTIF() accepts the cell range A2:A12 as its two mandatory arguments, range and criteria. So, it counts the number of appearances of each value of the cell range A2:A12 within the same specified range and returns the output as an array, {2;3;3;2;2;2;2;2;2;2;3}.

Next, the SUM() input becomes 1/{2;3;3;2;2;2;2;2;2;2;3}. So, the array values divide the numerator to return the array, {0.5;0.333333333333333;0. 333333333333333;0.5;0.5;0.5;0.5;0.5;0.5;0.5;0. 333333333333333}.

Finally, the SUM() adds the values of the resulting array to return the total count of unique fruits in the specified range A2:A12 as 5. And the value of 5 indicates the first occurrence of the five duplicate values, Apple, Banana, Peach, Pear, and Watermelon.

What Is Counting Unique Values In Excel?

The count unique values in Excel techniques calculate the total number of distinct values in the specified range.
Users can count distinct values in Excel while working with sales, marketing, and financial data, which can be text, numeric, and in any data format.
We can use the SUM or SUMPRODUCT function with the COUNTIF function to determine the distinct values in a given cell range. And using the PivotTable option can also help obtain the required count.
We must use the COUNTIFS function within the SUM or SUMPRODUCT function to count distinct values with multiple criteria.

How To Count The Distinct Values In Excel?

There are three methods to count unique values in Excel as distinct data:

Using The SUM And COUNTIF Functions
Using The SUMPRODUCT And COUNTIF Functions
Count Unique Values In Excel Ignore Blanks

Method #1 – Using The SUM And COUNTIF Functions

The steps to count distinct values in Excel using the SUM and COUNTIF functions are as follows:

Select a target cell to display the output.
Enter the formula to count unique values in Excel as distinct values:

=SUM(1/COUNTIF(Cell_Range,Cell_Range))

The two arguments of the COUNTIF(), range and criteria, are the same source cell range.
Press Ctrl + Shift + Enter to execute the expression as an array formula and obtain the required count of distinct values in a specified cell range.

Method #2 – Using The SUMPRODUCT And COUNTIF Functions

The steps to count distinct values in a worksheet using the Excel SUMPRODUCT and COUNTIF functions are as follows:

Select a target cell to display the output.
Enter the formula to count unique values in Excel as distinct values:

=SUMPRODUCT(1/COUNTIF(Cell_Range,Cell_Range))

The two arguments of the COUNTIF(), range and criteria, are the same source cell range.

Press Enter to execute the formula and obtain the required count of distinct values in a specified cell range.

Method #3 – Count Unique Values In Excel Ignore Blanks

When the source cell range contains empty cells, the above mentioned methods result in an #DIV/0! Excel error.

The reason is that the COUNTIF() in the formulas will return an array with zeros corresponding to the empty cells. And when the expression tries dividing the numerator value of 1 with the array element 0 in the SUM() and SUMPRODUCT(), it results in the #DIV/0! error.

Thus, in such situations, we can use the following method to count unique Excel values by excluding the empty cells in the given range.

Select a target cell to display the output.
Enter the formula to count distinct values in Excel, excluding the empty cells of the range:

=SUM(1/COUNTIF(Cell_Range,Cell_Range&””))-1

=SUMPRODUCT(1/COUNTIF(Cell_Range,Cell_Range&””))-1

The two argument values of the COUNTIF(), range and criteria, are the same source cell range.

Press Ctrl + Shift + Enter to execute the first formula and Enter to execute the second one and obtain the required count of distinct values in a specified cell range. And the empty cells get excluded from the count.

Examples

The following examples explain the three methods of counting distinct values in a specified cell range.

Example #1 – Count Unique Excel Values By Using The SUM And COUNTIF Functions

The table below lists brands, products and cost details.

And the requirement is to count the unique brands listed in column A and display the output in cell B14.

Then, here is how to use the COUNTIF() within the SUM() in the target cell to obtain the required count.

Step 1: Choose cell B14 and enter the SUM().

=SUM(

Next, enter the SUM() argument, a value with one as the numerator and COUNTIF() as the denominator.

=SUM(1/COUNTIF(

Next, as we must count the unique brand names in the cell range A2:A11, we enter the range as the two arguments in the COUNTIF().

=SUM(1/COUNTIF(A2:A11,A2:A11))

And close the brackets.

Step 2: Press Ctrl + Shift + Enter to execute the abovementioned SUM() as an array formula and achieve the required output in the target cell, 4.

First, we supply the same source range as the two arguments in the COUNTIF(). So, the function counts the number of appearances of each value of the source range in the same range.

And since the source cell range specified as the range (first argument) contains ten values, the COUNTIF() output is an array of ten elements, {4;4;4;4;4;4;1;4;4;1}.

The array elements indicate that the first six values and the eighth and ninth values in the range A2:A11 appear four times in the range A2:A11. And the seventh and tenth values appear once in the range A2:A11.

So, the SUM() input becomes 1/{4;4;4;4;4;4;1;4;4;1}.

Next, the formula divides the numerator value of 1 by each array element to return an array of ten values, {0.25;0.25;0.25;0.25;0.25;0.25;1;0.25;0.25;1}.

The division ensures we count only one appearance of each value in the range.

Finally, the SUM() adds all the array values to return the value of 4. And the value 4 represents the sum of the first appearance of each brand, Apple, Samsung, Google, and OnePlus, in the given range.

Example #2 – Count Unique Excel Values By Using The SUMPRODUCT And COUNTIF Functions

The table below lists the top 20 richest cities in the world.

And the requirement is to count the total distinct countries in column C and display the output in cell F1.

Then, here is how to use the SUMPRODUCT() and COUNTIF() in the target cell to obtain the required count.

Step 1: Select cell F1 and enter the following SUMPRODUCT().

=SUMPRODUCT(1/COUNTIF(C2:C21,C2:C21))

Step 2: Press Enter to execute the SUMPRODUCT() and achieve the required output in the target cell, 12.

First, the COUNTIF() counts the number of appearances of each value of the source range in the same range.

And since the source cell range specified as the range (first argument) contains 20 values, the COUNTIF() output is an array of 20 elements, {5;1;5;1;1;5;3;3;3;2;5;1;1;2;5;1;2;1;2;1}.

The array elements indicate that the second, fourth, fifth, twelfth, thirteenth, sixteenth, eighteenth, and twentieth values in the range C2:C21 appear once in the same range. The tenth, fourteenth, seventeenth, and nineteenth appear twice, and the seventh, eighth, and ninth values appear thrice in the given range.

On the other hand, the first, third, sixth, eleventh and fifteenth values appear five times in the range C2:C21.

So, the SUMPRODUCT() input becomes 1/{5;1;5;1;1;5;3;3;3;2;5;1;1;2;5;1;2;1;2;1}.

Next, the formula divides the numerator value of 1 by each array element to return an array of 20 values, {0.2;1;0.2;1;1;0.2;0.3333;0.3333;0.3333;0.5;0.2;1;1;0.5;0.2;1;0.5;1;0.5;1}.

The division ensures we count only one appearance of each value in the range.

Finally, the SUMPRODUCT() adds all the array values to return the value of 12. And the output is the sum of the first occurrence of each country, USA, Japan, UK, Singapore, China, Australia, Canada, Germany, Switzerland, South Korea, France, and UAE, in the given range.

Example #3 – Count Unique Excel Values By Excluding The Empty Cells Of The Range

We shall see how to count unique values in Excel based on date, excluding empty cells in the range.

The table below lists items and their ordered quantity data and order dates.

However, some order dates are missing, and we must count the unique dates in column C. Assume the target cells are F2:F3.

Then, here is how to use the modified versions of the previously discussed formulas in the target cells to count unique values in Excel based on date, ignoring empty cells in the range.

Step 1: Choose cell F2 and enter the following SUM()-based formula.

=SUM(1/COUNTIF(C2:C15,C2:C15&””))-1

Step 2: Press Ctrl + Shift + Enter to execute the abovementioned SUM()-based expression as an array formula and achieve the required output in the target cell, 6.

Step 3: Select cell F3 and enter the SUMPRODUCT()-based formula.

=SUMPRODUCT(1/COUNTIF(C2:C15,C2:C15&””))-1

Step 4: Press Enter to execute the abovementioned SUMPRODUCT()-based formula and achieve the required output in the target cell.

However, the output in cell F3 is in a custom date format in Excel. So, select cell F3 and set Home → Number Format → General.

Thus, we will achieve the required output in the correct data format as 6.

Count Unique Excel Values - Example 3 - Step 4c

The SUM() and SUMPRODUCT() execute the same way as explained in the previous sections.

However, the criteria in the COUNTIF() ensures the number of blank cells’ occurrences in the specified range also get counted and displayed as array elements.

So, the two function outputs will be the sum of the array elements. And the sums will be one more than the actual unique values in the specified range, accounting for one occurrence of blank cells. And then, the formula deducts 1 from the sum values to exclude the blank cells’ one occurrence count.

Important Things To Note

When using the SUM() with the COUNTIF() to count unique values in Excel, press Ctrl + Shift + Enter to execute the function as an array formula.
When the source data range contains blanks, use the modified formulas to count the distinct values in a given range to avoid the #DIV/0! error.
If we must count unique values in a given range with multiple criteria. Then, use the Excel COUNTIFS function instead of the COUNTIF() in the SUM() or SUMPRODUCT()-based formula to count distinct values.

Frequently Asked Questions (FAQs)

1. How to count unique values in multiple columns in Excel?

We can count unique values in multiple columns in Excel using the SUMPRODUCT() and COUNTIFS().

For example, the table below lists employees and their designations.

And the requirement is to count and display the distinct employee-designation values in cell E1.

Then, the steps are as follows:

• Step 1: Select cell E1, enter SUMPRODUCT(), and press Enter.
=SUMPRODUCT((1/COUNTIFS(A2:A11,A2:A11,B2:B11,B2:B11)))

As the specified cell ranges contain 10 values, the COUNTIFS() returns an array of ten elements, {1;2;1;2;1;1;1;1;1;1}.

The above array elements suggest that all the employee-designation combination values, except the second and fourth values, appear once in the given ranges. On the other hand, the second and fourth employee-designation combination values occur twice in the given ranges.

Next, the SUMPRODUCT() input becomes 1/{1;2;1;2;1;1;1;1;1;1}. And the division results in an array of 10 elements, {1;0.5;1;0.5;1;1;1;1;1;1}.

Finally, the SUMPRODUCT() adds the above array elements to return a value of 9, representing the unique employee-designation values in the columns A and B cell ranges.

2. How do I count unique values in Excel pivot table?

You can count unique values in Excel pivot table using the following steps:

1) Add a helper column to the source dataset.

2) Update the first cell of the helper column using the following sample formula.
=IF(COUNTIF($A$2:A2,A2)>1,0,1), where the cell references are to the required corresponding source data in the same row as the cell in the helper column.

3) Using the fill handle, enter the formula in the remaining cells of the helper column.

4) Click in the source dataset, which includes the helper column, and select Insert → PivotTable to create a pivot table.

5) The PivotTable Fields pane will open.
Set the column field, where we must count the unique values, as Rows and the helper column field as Values in the PivotTable Fields pane.

The above steps will result in the pivot table showing unique values in the chosen column field.

3. How to count unique values in Excel with multiple criteria?

We can count unique values in Excel with multiple criteria using COUNTIFS().
While we can estimate the required count based on one or more criteria, we can determine the count of unique text and numeric values.

Download Template

This article must be helpful to understand the Count Unique Values In Excel, with its formula and examples. You can download the template here to use it instantly.

Count Unique Values In Excel Download

Count Unique Values In Excel

What Is Counting Unique Values In Excel?

Table of contents

Key Takeaways