The Ultimate Guide to Counting Unique Values in Excel

If you work with data, counting unique values is a must-have skill for ensuring data quality and generating better insights. In this guide, I’ll show you how to count unique values in Excel using traditional formulas. I'll also cover special cases, such as handling case-sensitivity or counting based on one or more conditions.

But what if you could skip the complex formulas altogether? We'll also explore Excelmatic's modern, AI-powered approach that gets you the same results with simple language commands.

First, let's clarify a common point of confusion: the difference between "unique" and "distinct" values. Understanding this is key to getting the accurate counts you need.

A Note on Unique Values vs. Distinct Values in Excel

Sometimes, the terms unique values and distinct values are used interchangeably, but they are not the same thing. So, here’s a quick comparison to help you understand their difference.

Distinct values are all 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, and 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. 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.

Methods to Count Unique Values in Excel

Let's look at the classic formula-based methods first, then see how Excelmatic can simplify the process.

Method 1: Use the UNIQUE() and COUNTA() Functions

The easiest formula-based way to count unique values is by combining the UNIQUE() and COUNTA() functions. The UNIQUE() function can be configured to extract only the values that appear once, and COUNTA() then counts them.

=COUNTA(UNIQUE(range, false, true))

The key is the third argument in the UNIQUE function, exactly_once, which we set to true.

For example, applying the following formula to a dataset in A2:A8 gives us the count of unique values.

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

Count unique values using UNIQUE() and COUNTA() function. Image by Author.

Note: If you used =COUNTA(UNIQUE(A2:A8)), you would get a distinct count, not a unique one. The third argument is crucial for finding values that appear only once.

Method 2: Use SUM(), IF(), and COUNTIF()

If you're using an older version of Excel that doesn't have the UNIQUE function, or if you prefer a different approach, you can combine SUM(), IF(), and COUNTIF().

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

For data in the range A2:A8, the formula would be:

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

Here’s how it works:

1. COUNTIF(A2:A8, A2:A8) counts the occurrences of each item in the list.
2. IF(...,=1,1,0) checks if the count for an item is exactly 1. If it is, it returns 1; otherwise, it returns 0.
3. SUM() adds up all the 1s to give you the final count of unique values.

Count unique values using SUM(), IF(), and COUNTIF(). Image by Author.

The AI-Powered Way: Counting with Excelmatic

Remembering nested formulas and their specific arguments can be a hassle for business users. If you prefer a more direct and intuitive approach, Excelmatic can be a game-changer.

With Excelmatic, you don't need to write any formulas. You simply state your request in plain language.

How to count unique values with Excelmatic:

1. Upload your file to the Excelmatic platform.
2. Ask your question. For example, type: "Count the unique values in the first column" or "How many names in my list appear only once?"
3. Get your answer. Excelmatic analyzes your data and provides the count instantly.

Comparison: Formulas vs. Excelmatic

Counting Unique Text and Numeric Values

Sometimes, your dataset contains mixed data types, and you need to analyze them independently. Here's how to do it with formulas and with Excelmatic.

Count Unique Text Values

To count only the unique text values, you can add an ISTEXT() check to the SUM/IF/COUNTIF formula.

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

Count unique text values. Image by Author.

Count Unique Numeric Values

Similarly, to count unique numbers, replace ISTEXT() with ISNUMBER().

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

Count unique numeric values. Image by Author.

The AI Approach for Text and Numbers

With Excelmatic, this task remains just as simple. You just add the data type to your request:

• For text: "Count the unique text values in column A."
• For numbers: "How many unique numbers are in the 'Sales' column?"

The AI handles the filtering for you, saving you from adding more complexity to your formulas.

Counting Unique Values with Conditions and Criteria

Things get more complex when you need to count unique values that meet specific criteria.

Count Unique Values Based on One Condition

To count unique values based on a single condition, you can use FILTER, UNIQUE, and ROWS.

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

For instance, to count unique employees in a specific department (listed in cell F1):

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

Count unique values with a condition. Image by Author.

Count Unique Values Based on Multiple Conditions

For multiple criteria, you just add them to the FILTER function. For example, to count unique employees in the department from F1 with a salary greater than the value in F2:

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

Count unique values with multiple criteria. Image by Author.

The AI Advantage for Conditional Counts

This is where Excelmatic truly shines for business analysis. The formulas above are powerful but can be intimidating and hard to debug. With Excelmatic, the request remains a simple sentence.

• Single Condition: "Count the unique employees in the 'Marketing' department."
• Multiple Conditions: "How many unique employees are in the 'Sales' department with a salary over $50,000?"

You can describe complex logic without ever touching a formula, dramatically speeding up your workflow for business reporting.

Case-Sensitive Unique Counts

By default, Excel formulas are not case-sensitive ("Apple" is the same as "apple"). To force a case-sensitive count, you need a helper column or a complex array formula involving EXACT.

First, create a helper column with this formula:

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

Then, count the results using COUNTIF:

=COUNTIF(B2:B11,"Unique")

Count case-sensitive unique values. Image by Author.

With Excelmatic, you just specify your need for case sensitivity in the prompt:

"Give me a case-sensitive count of unique values in the 'Product Code' column."

Best Practices for Counting Unique Values in Excel

Here are a few practices that will help you keep things accurate and efficient:

• Clean Your Data: Ensure there are no extra spaces or inconsistent formatting. The TRIM function can help.
• Handle Blanks: Your data range should not include empty cells if you don't want them counted, as they can mess up the result.
• Use Dynamic Ranges: If your data changes often, consider using Excel Tables or dynamic array functions to keep everything updated in real-time.
• Leverage AI: For complex or frequent requests, Excelmatic can reduce manual errors and save significant time for business users.

How to Count Distinct Values Instead

Now, for those who landed here looking to count distinct values (all different items, including one of each duplicate), here are the methods.

Method 1: Use the COUNTIF() and SUM() Functions

A classic formula for counting distinct values is:

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

This formula can be sensitive to blank cells. A more robust version uses SUMPRODUCT:

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

Count distinct values using COUNTIF() and SUM() functions. Image by Author.

Method 2: Use PivotTables for Distinct Counts

Excel 2013 and later versions have a built-in feature to count distinct values in a PivotTable.

1. Select your data and go to Insert > PivotTable.
2. In the dialog box, check the box for Add this data to the Data Model.
3. Drag your desired column into the Values field.
4. Click the field in the Values area and select Value Field Settings.
5. Scroll down and choose Distinct Count.

Count distinct values using PivotTable. Image by Author.

Method 3: Apply Advanced Filters

For a non-formula approach, you can use Advanced Filters to extract a list of distinct values and then count them.

1. Go to the Data tab and click Advanced.
2. Choose Copy to another location.
3. Select your list range and a destination cell in the Copy to field.
4. Check the Unique records only box and click OK.
5. Use the ROWS() or COUNTA() function on the resulting list to get the count.

Count distinct values using an Advanced Filter option. Image by Author.

Method 4: Implement VBA Macros

For repetitive tasks, a VBA macro can automate the process. Press Alt + F11 to open the VBA editor, insert a new module, and paste this code:

Function CountDistinct(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
    
    CountDistinct = dict.Count
End Function

Save the module, and you can now use =CountDistinct(A2:A8) as a custom function in your worksheet.

Count distinct values using the VBA editor. Image by Author.

The AI Alternative: Distinct Counts with Excelmatic

Just like with unique counts, getting a distinct count with Excelmatic is a simple request:

"Count the distinct fruits in my list" or "Show me the number of distinct customers."

Method Comparison: Counting Distinct Values

Common Issues and Troubleshooting

• Hidden Spaces: Extra spaces can cause Excel to treat identical values as different. Use the TRIM() function to clean your data first.
• Formula Errors: Functions like UNIQUE() are only available in Excel 365 and Excel 2021. If you have an older version, you'll need to use the SUMPRODUCT or PivotTable methods.
• AI Misinterpretation: While rare, an AI might misinterpret a very ambiguous prompt. Being slightly more specific (e.g., "count distinct values in column B") usually resolves this.

Final Thoughts

We’ve covered a wide range of methods for counting unique and distinct values, from classic formulas and PivotTables to modern VBA macros and AI tools. Each method has its place, but the right choice depends on your specific needs, your Excel version, and your comfort level with formulas.

For business professionals who need quick, accurate results without technical complexity, Excelmatic provides a revolutionary approach. By using simple language commands, you can accomplish complex counting tasks instantly—eliminating formula errors and saving valuable time for data analysis and decision-making.

Ready to simplify your unique value counting in Excel?

Start using Excelmatic today and experience instant, AI-powered data analysis.

How do I count unique values without counting blanks?

You can use this formula, which filters out blank cells before counting:

=COUNTA(UNIQUE(FILTER(range, range<>"")))

With Excelmatic, it usually ignores blanks by default, or you can specify "count unique values and ignore blanks."

How do I highlight unique values using Conditional Formatting?

Select the range of data and go to the Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose Unique from the dropdown menu in the pop-up window and click OK.

Can I extract unique values using the Remove Duplicates option in Excel?

Yes, but be careful. The Data > Remove Duplicates tool actually extracts distinct values (it keeps one copy of each value), not unique values (values that appear only once). To use it:

1. Copy your data to a new location to preserve the original.
2. Select the copied range, go to the Data tab, and click Remove Duplicates.
3. Click OK, and Excel will remove duplicate rows, leaving only a distinct list.