How to Count Unique and Distinct Values in Excel

While dealing with data in Excel, duplicate items can confuse you and lead to miscalculations. For example, you might end up concluding a total “of 12 Participants” in a competition when, in fact, there are “10 Participants.”

This is why knowing how to count only Unique and Distinct Items from the data is an important skill.

Also, even though the words “Unique” and “Distinct” may seem similar, there’s a major difference between them.

So, first, we will dig into what each value means such that you get a precise unique count for your data. Then, you can follow the methods as needed.

Distinct Vs Unique — Know the Difference

Although while counting unique items, your only motive is to exclude the duplicate values, you must know the difference between “Unique” and “Distinct” first. 

Why? Because for each count, you’ll get a different result.

If you want to count only single items from the lists, it means “Unique.” For example, in the list (Outlook, Excel, Excel, Microsoft Word), Outlook and Microsoft Word are unique items. So, when you count, it’ll result in 2.

On the contrary, “Distinct” means counting the separate or different Values only. For Instance, again, in the same lists (Outlook, Excel, Excel, Microsoft Word), Outlook, Excel, and Microsoft Word is a Distinct item. So, the count result would be 3 this time. 

How to Count Unique Values in Excel?

Count Unique Values in Column

Assuming you have a list of text strings in a single column, I will use the SUM, IF, and COUNTIF nested together to perform this. 

Formula: 

=SUM(IF(COUNTIF(range, criteria)=1, value_if_true, value_if_false))

Example: 

Suppose, I have lists of names from cell B2 through B8. Let’s ignore duplicates and return only the unique items. For that, I used the below formula.

=SUM(IF(COUNTIF(B2:B8, B2:B8)=1, 1, 0))

In the COUNTIF function, we have specified the formula to count only single items. The IF function returns 1 when the test is true and 0 when not. Finally, the SUM function returns the total count of numbers resulted by the IF function. 

Since Excel, Teams, and Microsoft Powerpoint are unique, the formula returned 3. 

Count Unique Text Strings or Numbers Only

There can be instances when you have both numbers and text strings in the Column. 

Count Text Strings Only

To exclude numbers and count only text strings, we will use the SUM, IF, ISTEXT, and COUNTIF functions.

If you’ve noticed, here, only the ISTEXT function is extra. The rest of the functions are the same as above.

Formula: 

=SUM(IF(ISTEXT(value) * COUNTIF(range, criteria)=1, value_if_true, value_if_false))

Example:

Here, I have lists of Columns including duplicates of text values and numbers. Let’s count only the unique names from the column. 

=SUM(IF(ISTEXT(B2:B10)*COUNTIF(B2:B10,B2:B10)=1,1,0))

In the formula, by inserting the ISTEXT function, we have set the formula to take only texts. So, for output, we got 3. 

Count Numbers Only

Similarly, now, let us count only the unique numbers from the lists. This time, I’ll enter the ISNUMBER function instead of ISTEXT. 

For that, here’s the formula I will be using 

=SUM(IF(ISNUMBER(B2:B10)*COUNTIF(B2:B10,B2:B10)=1,1,0))

The formula counted unique numbers and resulted in 2.

Count Case-Sensitive Texts

In both above cases, the formula does not take the case-sensitive texts differently. So, if you want to count only the case-sensitive unique values, here’s the formula you can use. 

=SUMPRODUCT(1/COUNTIF(B2:B8, B2:B8))

As a result, I got 5. 

How to Count Distinct Values in Excel?

Since we already discussed what Distinct values mean above, let us now look into the formulas to count them. 

Count Distinct Values in Column

To count the Distinct Values in the Column, we will use the COUNTA and UNIQUE functions nested together. 

Example: 

Here, I have lists of items with Duplicates. To count and return the distinct values, enter the formula below. 

=COUNTA(UNIQUE(B2:B10))

The formula counted all distinct texts and numerical items. Then, returned 7. 

Count Distinct Texts or Numbers Only

Let’s assume, you need to count only distinct numbers or texts only from the range. 

Count Each Distinct Values

Assuming you need to identify each count of distinct values. To do that, you can use the Pivot Table.

1. On your sheet, select ranges.
2. Click on Insert Tab > Pivot Table.
   
3. Tick the box for Add this data to the Data Model and click OK.
   
4. Now, go to Another Sheet. On PivotTable Fields, checkmark Ranges.
   
5. Select all Sum of Amount Columns in PivotTable and right-click on it. Choose Value Field Settings.
   
6. Below Summarize the value field by, pick Distinct Count and click OK. 
   
7. You’ll see the Distinct Count of Amounts for each item.
   

Count Distinct Values Based on Criteria

Next, you can also count the Distinct Items based on a criteria. For example, count the number of unique items that are equal to VBA. It is similar to counting items based on 1 column.

To do that, we will nest the IFERROR, ROW, UNIQUE, and FILTER functions together. 

Example: 

From the given data, let’s count the names which is equal to VBA. Here’s the formula

=IFERROR(ROWS(UNIQUE(FILTER(D9:D15,E9:E15 =G6))), 0)

In this formula, we have specified the FILTER function to filter out the names with VBA. The UNIQUE function then discards the duplicates. Finally, the ROWS function returns the total row. In case the formula returns an error, we have set the IFERROR to return 0.

Here, we got 2. 

Count Distinct Values Without Blank Cells

While counting, many formulas take blank cells as unique and include them too. 

Therefore, for users having a blank cell, there’s a separate formula to exclude empty cells and count only the non-blank cells. 

I know the formula may seem a bit long and intimidating. But, here, we are just using the SUM, IF, FREQUENCY, LEN, and MATCH functions nested together. 

This is the formula I used to skip blank cells when counting the unique. Remember, it will count both Unique Texts and Numbers. 

=SUM(IF(FREQUENCY(IF(LEN(D9:D15)>0, MATCH(D9:D15, D9:D15, 0), ""), IF(LEN(D9:D15) > 0, MATCH(D9:D15, D9:D15, 0), ""))>0, 1))

We got 3 as a result. 

Let’s take the formula simply as =SUM(number1, number 2) function and break it down to understand it. 

Number 1: IF(FREQUENCY(IF(LEN(D9:D15)>0, MATCH(D9:D15, D9:D15, 0), “”)

• IF(LEN(D9:D15)>0, MATCH(D9:D15, D9:D15, 0): The IF function tests the LEN(D9:D15) > 0 condition. When the condition is met, it returns the position resulted the MATCH function. Or, “” null string when the value is False. 
• IF(FREQUENCY(IF(LEN(D9:D15)>0, MATCH(D9:D15, D9:D15, 0), “”): Here, the FREQUENCY function returns the occurrence number of values. Then, again, IF function returns 1 when the FREQUENCY(IF(LEN(D9:D15)>0 condition is TRUE. It returns “” for FALSE value.

Number 2 : IF(LEN(D9:D15) > 0, MATCH(D9:D15, D9:D15, 0), “”))>0, 1)

In this formula, the IF function tests LEN(D9:D15) > 0 criteria and returns the position number resulting from the MATCH function when the condition is true. In case, the condition is false, it results in “” null string.

Finally, the SUM function adds the numbers and results in the total unique counts without the empty cells. 

Count both Distinct and Unique Values with Power Query

If you want to count both Distinct and Unique items at once, use Excel’s Power Query feature. 

1. Simply select your data and head to the Data Tab.
2. From the Get & Transform Data group, click on Get Data. When prompted, choose Yes.
   
3. Now, on the Power Query Editor window, navigate to the View Tab. Check the box for Column Distribution. It’ll show the total number of distinct and unique items.
   

By now, you must have learned how to count unique and distinct items. If you find the need to find the duplicates, we have a detailed article on “How to count duplicates in Excel.”