Advertisement
Table of Contents
Related Topics
Popular in Excel Tips
Quick Tip

Use Ctrl+; to insert today's date in any cell quickly.

Advertisement
Advertisement
Previous

3 Methods: How to Count Unique Values in Excel

Excel Tips
Aug 23, 2025 1 min read
Count Unique Values in Excel
Table of Contents

As you know, Excel is a powerful tool, but sometimes it can be confusing, especially when we need to perform a specific task such as counting the unique values. You can use the UNIQUE function to return the unique values, but let’s learn three different formulas to count unique values in Excel which will just return us the unique number.

Advertisement

Why Counting Unique Value Matters

To count the unique values from your dataset is a crucial and most required task in data analysis. Assume you have a list of customers or products, and you need to find out how many unique entries are there. This could be very useful for inventory tracking, customer profiling, or even useful in financial reporting. Instead of manually checking, we can rely on a few Excel formulas to do the work for us. Below is a step-by-step guide with examples, so even if you are new to Excel, you can understand and use these formulas effectively.

Methods

Method 1: Formula Using IF, COUNTA, UNIQUE, and Filter (latest version of Excel)

Formula:

=IF(COUNTA(A2:A100)=0,0,COUNTA(UNIQUE(FILTER(A2:A100&"",A2:A100<>""))))

How It Works:

  • COUNTA(A2:A100) checks if the range is empty. If the formula finds the empty cells, it will return 0.
  • FILTER(A2:A100&””,A2:A100<>””) removes empty cells from the selected range.
  • UNIQUE(…) then finds all the unique values from the filtered list.
  • COUNTA(…) counts these unique values.
Method illustration
With this dataset in range A2:A10, the formula will give us a result of 6, counting only the unique values: Apple, Mango, Orange, Banana, Grape, and Pineapple.

When to Use:

This formula is very efficient when you have blank cells in your dataset because it will filter the blank cells automatically and return only cells that have data. It is especially useful in newer versions of Excel (Excel 365 and Excel 2019), where the UNIQUE and FILTER functions are available.

Method 2: Formula Using SUMPRODUCT and COUNTIF

Formula:

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))

How It Works:

  • (A2:A100<>””) checks and ignores the non-blank cells.
  • COUNTIF(A2:A100, A2:A100&””) counts each value’s occurrences in the selected range.
  • The division operation helps count only the unique values by ignoring the duplicates.
Method illustration
With the same dataset as above, the formula will return 6 as the unique count.

When to Use:

This formula works in all the versions of Excel, so it is a perfect solution for those using older versions of Excel like Excel 2016 or 2013.

Method 3: Formula Using SUM and COUNTIF

Formula:

=SUM((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))

How It Works:

  • Similar to the SUMPRODUCT formula, this one also uses (A2:A100<>””) to ignore the blank cells.
  • The COUNTIF part counts occurrences, and the division by COUNTIF counts only the unique items.
  • Wrapping it in SUM finalizes the calculation, returning the unique count.

When to Use:

This formula is also compatible with all versions of Excel and is a handy alternative if you want to use SUM instead of SUMPRODUCT.

Tags: Excel Tips
Share:
Advertisement

Comments (0)

Leave a Comment

Comments are moderated before publishing.

No comments yet. Be the first to comment!

Related Posts

Excel Tips

How to Insert Rows in Excel: 6 Methods
How to Insert Rows in Excel: 6 Methods

Many find adding rows in Excel is a simple task, but do you know there are several efficient ways to Insert Rows in Excel? You might be inserting a si...

Aug 23, 2025 Read More
Quick Tip

Press Alt+= to auto-sum selected cells without typing formulas.

Advertisement
Advertisement