Counting Unique Values in Excel: Formulas and Methods

Formulas & functions · Updated November 22, 2026

To count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) when your values are in A2:A100. It returns the number of different entries, ignoring empty cells; for example, a list containing Maya, Leo, Maya, and a blank has a count of 2.

What “unique” means in Excel

In spreadsheet questions, “count unique values” usually means count each different value once, regardless of how many times it appears. Excel documentation and functions also use “unique” to mean values that occur exactly once. Those are different tasks: if Maya appears twice, she is included in a distinct-value count but excluded from an exactly-once count.

The formula above answers the common distinct-count question. It first filters out blank cells, then returns one copy of each remaining value, and finally counts the results. This is useful for questions such as how many customers, product codes, departments, or dates appear in a list. It counts values, not rows that are different across several columns; that requires a formula or PivotTable designed for records.

Count distinct values with UNIQUE and FILTER

In Microsoft 365, UNIQUE creates a list of distinct entries and FILTER lets you remove blanks before counting. With values in A2:A100, the formula =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) returns a single number. IFERROR makes the result 0 if the range contains no nonblank values, because FILTER otherwise has nothing to return.

For a sample range A2:A8 containing Maya, Leo, Maya, Nia, Leo, a blank, and Omar, the formula returns 4: Maya, Leo, Nia, and Omar are the four distinct nonblank entries. If you want to see the distinct list rather than count it, and values are in A2:A100, use =UNIQUE(FILTER(A2:A100,A2:A100<>"")). The list spills into cells below the formula, so leave that output area empty.

UNIQUE has an optional third argument for values that occur exactly once. With entries in A2:A100, =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""),FALSE,TRUE)),0) counts only entries appearing once. In the sample list above, Nia and Omar each appear once, so the result is 2. This is not the usual way to count distinct values; omit the third argument, or leave it FALSE, when repeated values should still count once.

Count unique values across multiple columns

If your data consists of several columns and you want distinct combinations, decide what counts as one record. For example, the pair of a customer and a product may be the unit you need to count. Counting each column separately and adding the results will not give the number of distinct pairs, because the same customer or product can appear in many combinations.

In Microsoft 365, UNIQUE can return distinct rows from a multi-column range. With customer names in A2:A100 and product names in B2:B100, use =ROWS(UNIQUE(FILTER(A2:B100,A2:A100<>""))) to count distinct customer-product pairs, provided every valid record has a customer name. FILTER removes rows with a blank customer; if blank customers can be legitimate records, use a different test column or define the record rule explicitly. Check that the columns in the filtered range have matching row boundaries.

For a distinct count of one column within a category, add a condition to FILTER. With categories in A2:A100, names in B2:B100, and the category to examine in D2, use =IFERROR(ROWS(UNIQUE(FILTER(B2:B100,(A2:A100=D2)*(B2:B100<>"")))),0). This counts nonblank names for the category shown in D2. The multiplication combines the two TRUE/FALSE tests as an AND condition.

Use a PivotTable for a distinct count

A PivotTable is a good option when you want a distinct count alongside other summaries, such as sales totals by region. In Excel for Microsoft 365 on Windows, select the data, choose Insert > PivotTable, and in the creation dialog select Add this data to the Data Model if that option is available. Add the field to count to the Values area, open its value-field settings, and select Distinct Count. The distinct-count option depends on using the Data Model; it is not available in every ordinary PivotTable setup.

PivotTables are especially useful for exploring grouped results without writing a separate formula for every category. They can also summarize multiple fields, but they need refreshing after source data changes. On Excel for the web or Mac, available Data Model and PivotTable options can differ by version and platform, so check the options in the PivotTable dialog. A worksheet formula is often the more portable choice when you need a result that updates with the range.

Methods for older Excel versions

UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019. One classic formula for counting distinct nonblank entries is =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")), assuming the list is in A2:A100. It returns a distinct count without requiring the dynamic-array functions. For the sample list of Maya, Leo, Maya, Nia, Leo, a blank, and Omar, it returns 4.

This older formula is less straightforward to maintain, and COUNTIF treats the asterisk and question mark as wildcard characters in criteria. That can make results surprising when text values contain those characters. If that matters, use a PivotTable distinct count or copy a deduplicated list and count it. Another built-in option is Data > Advanced, then choose to copy unique records to another location; count the resulting list while excluding its header, if present. Advanced Filter creates a copied result rather than a live dynamic list, so repeat the operation after the source changes.

Common counting problems and how to fix them

  • Blanks: Decide whether blanks should count. The main formula excludes empty cells, including cells that contain formulas returning an empty string. If a blank should be a category, do not filter it out; note that UNIQUE can return a blank entry.
  • Extra spaces: “Maya” and “Maya ” can be treated as different text because one has a trailing space. Clean the source data or use a helper column with TRIM before counting. TRIM removes ordinary extra spaces but does not remove every possible nonprinting character.
  • Numbers stored as text: A number entered as text may not behave like the same number entered as a numeric value. Standardize the column before counting if entries such as 25 and text “25” should be considered identical.
  • Capitalization: UNIQUE and common COUNTIF-based methods do not provide case-sensitive distinct counts. “MAYA” and “Maya” are normally treated as the same value for this purpose. If capitalization carries meaning, use a specifically case-sensitive approach rather than assuming the default formula distinguishes it.
  • Error values: An error in the source can flow through FILTER or UNIQUE and prevent the count from calculating. Find and correct the source error, or explicitly decide how errors should be treated before counting.
  • Changing data size: A fixed reference such as A2:A100 will not include new entries below row 100. Convert the source to an Excel Table with Insert > Table and use structured references, or select a range large enough for expected additions.

Choose the right method for the job

Use a dynamic-array formula when you need a compact count that updates as the source changes. Use UNIQUE by itself when the actual list matters, not just its size. Use a PivotTable when the count belongs in a report with other grouped measures, and use Advanced Filter when you only need a one-time deduplicated copy. Older Excel versions can use the SUMPRODUCT formula, but the wildcard caveat and less transparent logic make it worth checking the result against a visible unique list.

It is also important to distinguish counting unique values from removing duplicates. Data > Remove Duplicates changes the selected source data; it is a cleanup operation, not a formula for reporting a count. Make a copy or use a separate output area if you want to preserve the original records. If you only want to identify duplicates visually, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values; that highlights repeats but does not calculate the number of distinct entries.

Frequently Asked Questions

How do I count unique values but ignore blanks?
With values in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). FILTER excludes blanks before UNIQUE creates the distinct list, and IFERROR returns 0 when there are no nonblank entries.

How do I count values that appear only once?
With values in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""),FALSE,TRUE)),0). The final TRUE asks UNIQUE for items that occur exactly once, rather than one copy of every distinct item.

Does Excel count uppercase and lowercase text as different?
Not with the standard UNIQUE and COUNTIF-based methods described here. They treat capitalization differences as equivalent for ordinary distinct counting, so “Oak” and “OAK” count as one value.

Can I count distinct values in an older version of Excel?
Yes. Use the SUMPRODUCT and COUNTIF formula in the older-version section, a PivotTable distinct count where the Data Model is supported, or Advanced Filter to make a unique list and count it. The exact options depend on your Excel version.

Why does my count differ from the number of rows?
Rows include repeated entries, while a distinct count includes each value once. The count can also differ because blanks are excluded, text contains unnoticed spaces, numbers are stored inconsistently, or your formula range omits some rows. Review those conditions before changing the formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.