How to Count Unique Values in Excel: What You Need to Know

The ideas sometimes get confused, but unique values and distinct values are not the same thing.

• Distinct values are the different values in a dataset, with duplicates removed. For example, in the list A, A, B, C, C, D, the distinct values are A, B, C, D. 

• Unique Values, on the other hand, are values that occur only once in the dataset. Using the same example, the unique values are B and D (because A and C appear more than once).

For the first half of the article, I will focus on different ways to count unique values in Excel. But, if you have landed here by accident and are really looking to count distinct values, I have you covered for this, also, if you want to scroll to the later section.

Two Ways to Count Unique Values in Excel

There are two common ways to count unique values in Excel.

How to count unique values in Excel using the UNIQUE() function

The easiest way to count unique values is by using the UNIQUE() function and the COUNTA() function. The UNIQUE() function extracts all unique values from a range, and COUNTA() counts how many unique values are present. 

COUNTA(UNIQUE(range, false, true))Powered By 

For example, I applied the following formula to a small dataset, and it gave me unique values.

=COUNTA(UNIQUE(A2:A8, false, true))Powered By 

If you used this syntax instead, =COUNTA(UNIQUE(A2:A8)), which doesn’t have the third parameter that we set as TRUE, then we would get back as our result a distinct count, not a unique one. Setting TRUE in the third argument tells the function to return a unique count. Both things are within the UNIQUE() function, so it may be a bit confusing at first.

How to count unique values in Excel with SUM() and COUNTIF()

If you are a little unsure of the nuances of the UNIQUE() function, or if you don’t have Excel 365, know that we can also combine SUM() with IF() and COUNTIF() to count the unique values.

=SUM(IF(COUNTIF(range, range)=1,1,0))Powered By 

For example, I have data in the range A2:A8 and want to count the unique values, so I write the following formula:

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))Powered By 

Here COUNTIF() goes through the list and checks how many times each name appears. If a name shows up just once, it counts it as 1. If it appears more than once, it gets a higher number. Then, IF() filters those results to keep the 1s as they are but turns everything else into 0. At last, SUM() adds up all the 1s and gives us the total count of unique values.

How to Count Unique Data Types in Excel

Sometimes, our dataset contains mixed data types and we have to analyze them independently. This may look a bit daunting at first, but it’s possible in Excel. Let’s look at two methods — one for counting unique text values and another for unique numbers.

How to count unique text values in Excel

If you want to count unique text values, combine the ISTEXT(), COUNTIF(), and SUM() functions. ISTEXT() checks if a value is a text, while COUNTIF() counts how many times each value appears, and SUM() totals the distinct text entries. 

I applied the following formula on a sample data, and it instantly showed me how many unique text values are present.

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))Powered By 

How to count unique numeric values in Excel

If you want to count unique numbers instead of text, use the same formula but replace the ISTEXT() with ISNUMBER().

ISNUMBER() will only consider numeric values, while COUNTIF() and SUM() handle the uniqueness. I tweaked the above formula on the same data, and it now shows me the number of unique numeric values only:

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))Powered By 

How to Count Unique Values with Conditions in Excel

Now that we’ve covered the basic methods for counting unique values, let’s explore some advanced techniques.

How to count unique values based on conditions

To count the unique values based on a specific condition, I use this syntax:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)Powered By 

In this formula, FILTER() filters out the range based on specific criteria, UNIQUE() removes duplicates, ROWS() counts the results, and IFERROR() prevents errors by returning Not Found if no matches are found.

Let’s say I have a list of Employees and their Departments, and I need to count how many unique employees work in a specific department. I will use the following formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")Powered By 

Here, A2:A10 represents the range of employee names I want to filter and B2:B10=F1 checks which employees belong to the department, which is written in F1.

How to count unique rows based on multiple columns

If I want to count unique employees in a specific department whose salaries are less than $50,000, I use this formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")Powered By 

Here, A2:A10 represents the range of employee names, B2:B10=F1 checks which employees belong to the department, written in F1, and C2:C10>F2 checks if the salary is less than the amount given in F2.

How to handle case-sensitive unique counts

By default, Excel is case-sensitive. For example, Apple and APPLE are considered different. To spot such cases, I create a helper column with the following formula:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")Powered By 

And now, to get the count of unique values, I use the COUNTIF() function like this:

=COUNTIF(B2:B11,"Unique")Powered By 

How to Count Distinct Values Instead

Now, as a final section, in case you had really wanted to count distinct values instead, I will show you several methods to count distinct values. But first, here is a table so you can see the differences:

How to count distinct values in Excel using COUNTIF() and SUM()

To count distinct values in Excel, you can combine the COUNTIF() and SUM() functions. The COUNTIF() function checks how many times each value appears in a given range. Then, SUM() adds up the values returned by COUNTIF(), giving the total number of distinct entries. 

=SUM(1/COUNTIF(range, range))Powered By 

Let’s say I want to find the distinct values in the range A2:A8. I enter the following formula:

=SUM(1/COUNTIF(A2:A8, A2:A8))Powered By 

Here, the COUNTIF() function checks how many times each value appears in a list.

1/COUNTIF(A2:A8, A2:A8) divides 1 by the number of times each value appears. For example, if a number appears twice, each instance becomes 0.5 (1/2). If it appears three times, it will be 0.33 (1/3). 

Since we don’t want these fractions, we can use the SUM() or SUMPRODUCT() to add up all the values. Every duplicate’s fractions combine to 1, and distinct values stay 1 since 1/1 = 1:

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))Powered By 

How to count distinct values in Excel using PivotTables

Excel 2013 and Excel 2016 have included a built-in feature to count distinct values in a PivotTable. To make use of this, you first have to create a PivotTable by selecting data: Go to Insert and select PivotTable. A dialog box will appear — from this dialog box, choose Existing Worksheet and checkmark the Add this data to the Data Model box.

Next, drag your desired column into the Values field. In my case, I drag the Fruits column because I want to count its distinct values. Then, click on Value Field Settings from the drop-down menu. A pop-up will appear — from there, select Distinct Count. You can even give this column a custom name, but that’s optional.

How to count distinct values in Excel using filters

If you don’t like working with formulas, use the advanced filters instead. 

In this example, I have a dataset and I want to find distinct values using advanced filters. Now, for this, I select the range from where I want to find the particular value. Then, I go to the Data tab and click Advanced. 

Now, in the Advanced Filter dialog box, I choose Copy to another location, and in the Copy to field, I enter the destination cell where I want my list to appear. Then check the Unique Records Only box and click OK.

Once I have a list of unique values, I then calculate the count of these unique values by using the ROWS() function:

=ROWS(D2:D5)Powered By 

Even though the option in the below image reads Unique records only, it actually extracts distinct values. 

How to count distinct values in Excel using VBA macros

If you want to count distinct values repeatedly, use VBA (Visual Basic for Applications), where we write code once, and it automates the whole process every time we call the function with its name.

Here’s how you can use this: press Alt + F11 and go to the Module option under the Insert tab. A new module will appear, and you can paste your code here. 

This is the code I created to count the distinct values:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End FunctionPowered By 

Then, press Ctrl + S to save it and ALt + Q to close the VBA editor. Now, you can call this custom function anywhere in the sheet and specify the range from which to find the distinct values. In my case, it’s called CountUnique() so every time I call this function with a specified range, it gives me a count value:

=CountUnique(A2:A8)Powered By 

I’ve covered key formulas, PivotTables, and VBA macros for counting unique and distinct values. Each method has its benefits, but you should choose the one that suits your needs and supports your Excel version.

If you want to sharpen your Excel skills, check out our Excel Fundamentals skill track and Data Preparation in Excel course.