Prem Rawat

Peace is Possible

— Prem Rawat

True peace begins within you. Discover the peace that already exists inside yourself, and let it bring more love, happiness and understanding into your life.

Visit YouTube Channel

How to Count Unique Values in Excel (7 Easy Methods)

Learn how to count unique values in Excel using UNIQUE, COUNTA, COUNTIF, and PivotTables. Includes practical examples and formulas to ignore blanks.

 

How to Count Unique Values in Excel (7 Easy Methods)

   Learn how to count unique values in Excel using simple formulas and PivotTables. Explore UNIQUE, ROWS, FILTER, SUMPRODUCT, and COUNTIF with practical examples to identify distinct text, numbers, and combinations while ignoring duplicates and blank cells.

How to Count Unique Values in Excel: A Complete Guide with Formulas and Examples

Counting unique values in Excel is a common task when working with customer lists, sales records, employee databases, product inventories, or any dataset containing repeated entries.

For example, imagine you have a spreadsheet containing 500 customer orders. Some customers appear multiple times because they placed several orders. If you want to know how many different customers made purchases, simply counting the cells will give you the wrong result. You need to count each customer only once.

Fortunately, Excel provides several ways to count unique values. You can use modern functions such as UNIQUE and COUNTA, or older formulas that work in earlier Excel versions.

In this guide, you'll learn different methods to count unique values in Excel, understand when to use each formula, and explore practical examples that you can apply to your own spreadsheets.

What Does Counting Unique Values Mean in Excel?

Counting unique values means identifying and calculating the number of different entries in a range while ignoring repeated occurrences.

For example, consider the following list of products:

Product
Laptop
Mouse
Keyboard
Laptop
Monitor
Mouse
Headphones
Keyboard

Although the list contains eight entries, there are only five different products.

The unique products are:

  • Laptop

  • Mouse

  • Keyboard

  • Monitor

  • Headphones

Therefore, the unique count is 5.

This technique is particularly useful when analysing customer records, tracking products, reviewing sales transactions, and preparing business reports.

Method 1: Count Unique Values Using UNIQUE and COUNTA

The easiest way to count unique values in modern Excel is to combine the UNIQUE and COUNTA functions.

The UNIQUE function extracts distinct entries from a range, while COUNTA counts the resulting non-empty cells.

Formula:

=COUNTA(UNIQUE(A2:A9))

How to use it:

  1. Enter your data in cells A2.

  2. Select an empty cell where you want the result.

  3. Enter the formula shown above.

  4. Press Enter.

Excel will identify the distinct entries and return their total count.

For example, if your product list contains Laptop, Mouse, Keyboard, Laptop, Monitor, Mouse, Keyboard, and Headphones, the formula returns 5.

Why use this method?

  • Simple and easy to understand.

  • Automatically updates when the source data changes.

  • Works with text, numbers, and mixed data.

  • Does not require helper columns.

Note: This formula works in Microsoft 365, Excel 2021, Excel 2024, and other versions that support the UNIQUE function.

Method 2: Count Unique Values Using UNIQUE and ROWS

Another useful approach is to combine UNIQUE with the ROWS function.

Instead of counting non-empty cells, this formula counts the number of rows returned by the UNIQUE function.

Formula:

=ROWS(UNIQUE(A2:A9))

This formula returns the number of distinct entries in the selected range.

For example, if A2 contains eight product entries with three repeated products, Excel returns 5.

When should you use this formula?

Use ROWS when you want to count the number of distinct entries returned by UNIQUE. It is particularly useful when working with lists where the extracted results are arranged vertically.

Method 3: Count Unique Values While Ignoring Blank Cells

Sometimes, your dataset contains empty cells. Depending on your requirements, you may want to exclude those blanks from the unique count.

You can combine UNIQUE, FILTER, and ROWS to count only non-empty entries.

Formula:

=ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

How it works:

  • FILTER removes blank cells from the original range.

  • UNIQUE extracts distinct values from the remaining entries.

  • ROWS calculates how many unique entries are left.

For example, imagine your customer list contains 100 rows, but 15 cells are empty and several customer names are repeated. This formula counts only the different customer names and ignores blank cells.

This is a useful option when working with incomplete customer databases, inventory records, or imported spreadsheets.

Method 4: Count Unique Values Using SUMPRODUCT and COUNTIF

If you're using an older version of Excel that does not support the UNIQUE function, you can use SUMPRODUCT and COUNTIF together.

Formula:

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

This formula calculates the number of distinct non-empty entries in a range without requiring modern dynamic array functions.

Example:

Suppose your spreadsheet contains the following customer names:

Customer Name
John
Sarah
David
John
Michael
Sarah
Emma
David

The formula returns 5 because there are five different customer names.

Advantages:

  • Useful in older Excel versions.

  • Does not require a helper column.

  • Ignores blank cells.

  • Works with text and numeric entries.

However, this formula is more complicated than the modern UNIQUE approach. If your Excel version supports dynamic arrays, using UNIQUE is generally easier.

Method 5: Count Unique Numeric Values in Excel

If your dataset contains numbers rather than text, you can still use the UNIQUE function to count different entries.

Formula:

=ROWS(UNIQUE(A2:A100))

For example, imagine you have a sales report containing these order quantities:

Quantity
10
20
15
10
25
20
30
15

Although there are eight records, only five different quantities appear.

The formula returns 5.

If your range contains blank cells and you want to exclude them, use:

=ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

This approach is useful for analysing sales quantities, transaction amounts, product codes, and employee IDs.

Method 6: Count Unique Values Based on Multiple Columns

Sometimes, you need to count unique combinations rather than individual values.

For example, a sales report may contain customer names and product names. You might want to know how many different customer-product combinations exist.

Consider this dataset:

CustomerProduct
JohnLaptop
SarahMouse
JohnLaptop
DavidKeyboard
SarahLaptop
JohnMouse

To count unique combinations across both columns, use:

=ROWS(UNIQUE(A2:B7))

Excel identifies each distinct combination of customer and product.

The result is 5 because the combination John–Laptop appears twice, while the other four combinations are different.

This formula is particularly useful for sales analysis, customer purchase reports, inventory management, and transaction tracking.

Method 7: Count Unique Values Using a PivotTable

If you prefer a visual approach instead of writing formulas, a PivotTable can help you analyse distinct records.

However, to calculate a true distinct count, you need to add your data to the Data Model.

Follow these steps:

  1. Select your dataset, including its column headings.

  2. Go to the Insert tab.

  3. Select PivotTable.

  4. Enable the Add this data to the Data Model option, if available.

  5. Choose where you want the PivotTable to appear.

  6. Drag the field you want to analyse into the Values area.

  7. Open Value Field Settings.

  8. Select Distinct Count.

  9. Click OK.

Excel will display the number of different values in your selected field.

For example, if your sales database contains 1,000 transactions from 250 different customers, the PivotTable can show a distinct customer count of 250.

When is this method useful?

PivotTables are especially helpful when working with large datasets or preparing interactive business reports. You can also combine distinct counts with filters, categories, and other fields to explore your data.

Difference Between UNIQUE and DISTINCT COUNT in Excel

Although both approaches help identify different values, they are not exactly the same.

FeatureUNIQUE FunctionDistinct Count
Main purposeExtracts different valuesCounts different values
OutputA list of unique entriesA numerical result
Excel versionExcel 2021 or later, Microsoft 365Available in supported Data Model PivotTables
Updates with source dataYes, automaticallyAfter refreshing the PivotTable when needed
Best useFormula-based calculationsBusiness reports and data analysis

The UNIQUE function is ideal when you want to generate a separate list of distinct entries. Distinct Count is more convenient when you need a summary number inside a PivotTable.

Common Mistakes to Avoid When Counting Unique Values

Even simple Excel formulas can produce unexpected results if your source data is not properly organised.

Here are some common issues to watch out for.

Extra Spaces in Text

Excel may treat entries such as John and John as different values because the second entry contains an additional space.

You can clean your data with the TRIM function before counting unique names.

Blank Cells

Some formulas may include a blank entry in the unique count. If you want to ignore blanks, use the FILTER method.

Different Text Formats

Entries such as Product A and Product A1 are different values. Check spelling and formatting before calculating your results.

Hidden or Filtered Rows

The UNIQUE function generally considers all cells in its referenced range, including hidden or filtered-out records. If you need a count based only on visible rows, you will need a different approach.

Incorrect Data Range

If your formula refers to A2 but your dataset extends to A100, some records will be missed. Consider using an Excel Table or structured references to make your formulas easier to maintain.

Best Excel Formula for Counting Unique Values

Choosing the right formula depends on your Excel version and the type of data you're analysing.

RequirementRecommended Formula or Method
Count unique text or numeric values=COUNTA(UNIQUE(A2:A100))
Count unique values using ROWS=ROWS(UNIQUE(A2:A100))
Ignore blank cells=ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
Older Excel versionsSUMPRODUCT with COUNTIF
Count unique combinations=ROWS(UNIQUE(A2:B100))
Analyse unique records visuallyPivotTable with Distinct Count

For most users with Microsoft 365 or Excel 2021 and later, the UNIQUE function combined with ROWS is an excellent starting point.

Frequently Asked Questions (FAQs)

1. How do I count unique values in Excel with one formula?

Use =ROWS(UNIQUE(A2:A100)) to count the number of different entries in a range.

2. Can Excel count unique values without counting blanks?

Yes. Use =ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))) to exclude empty cells.

3. Does the UNIQUE function work in older Excel versions?

No. The UNIQUE function is available in Microsoft 365, Excel 2021, Excel 2024, and supported newer versions. Older versions can use SUMPRODUCT and COUNTIF.

4. What is the difference between unique and distinct values?

In everyday Excel usage, these terms are often used interchangeably to mean different entries without repetition. However, the UNIQUE function also supports an option to return values that occur exactly once.

5. Can I count unique values from multiple columns?

Yes. Use =ROWS(UNIQUE(A2:B100)) to count distinct row combinations across two columns.

Final Thoughts

Counting unique values in Excel becomes much easier when you know which formula to use. For modern Excel versions, combining UNIQUE with ROWS is one of the simplest ways to calculate distinct entries.

If you regularly work with customer databases, sales reports, employee records, or inventory lists, these techniques can save time and reduce manual errors.

Start with the UNIQUE function, use FILTER when you need to exclude blanks, and consider PivotTables when analysing larger datasets. Once you understand these methods, handling duplicate records and identifying distinct values becomes a much simpler task.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content