## 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.

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.

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**.

##### Table of contents

###### Key Takeaways

- 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**

Or

**=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**.

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.

### Recommended Articles

This has been a guide to What Is Count Unique Values In Excel. We learn to do it using SUM & SUMPRODUCT with COUNTIF, & excluding empty cells, with examples. You can learn more from the following articles –

## Leave a Reply