Count Unique Values in Excel Without Duplicates

Formulas & functions · Updated November 19, 2026

To count distinct, nonblank values in Excel, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) when your list is in A2:A100. The formula counts each different value once, ignores blank cells, and returns 0 if no values qualify.

What “unique” means in an Excel count

In everyday spreadsheet questions, “count unique” usually means count the different values in a range, without counting repeats more than once. If a list contains Apple, Pear, Apple, and Banana, the distinct count is 3. The repeated Apple contributes one value to the count, not two.

Excel’s UNIQUE function returns a list of distinct entries. By default, it keeps one copy of each value that occurs at least once. That is different from asking for values that occur exactly one time. In the example above, the distinct values are Apple, Pear, and Banana, but only Banana appears exactly once. That distinction matters because “unique” is sometimes used to mean “non-repeating” in ordinary conversation.

The formula in this guide counts distinct values. It is designed for a single column, and it excludes blanks so empty cells do not inflate the result. You can adjust the range to match your data, but use the same start and end rows throughout the formula.

How to enter the formula

In Microsoft 365 or Excel 2021 and later, follow these steps:

  1. Put the values you want to count in one column, such as A2:A100. A header can sit above the range in A1.
  2. Click an empty cell where you want the result to appear.
  3. With the values in A2:A100, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) and press Enter.
  4. Change A2:A100 in every part of the formula if your list is in a different location.

FILTER first removes blank cells, UNIQUE produces one instance of each remaining value, and ROWS counts the results. IFERROR supplies 0 if there are no qualifying entries, since a filter with no matches can otherwise return an error. In Microsoft 365, Excel displays the result in the selected cell; there is no need to copy the formula down.

The formula works the same way in Excel for Mac when the version includes these functions. Excel for the web also supports dynamic array functions, although some surrounding features differ from the Windows desktop app. In older releases without UNIQUE and FILTER, use a PivotTable or another compatible method instead.

Why the formula filters out blanks

A plain COUNTA counts nonempty cells, not distinct values. If a range contains 100 populated cells, COUNTA returns 100 even if most entries repeat. Combining it with UNIQUE is a common shortcut, but blanks can be awkward: depending on how the range and dynamic array are handled, a blank may appear in the unique results as an empty-looking value or as zero. Filtering blanks first makes the intention explicit.

The test A2:A100<>"" excludes cells that are genuinely empty and cells whose formulas return an empty string. It does not remove cells containing spaces. A cell that looks blank but contains one or more spaces is text, so Excel treats it as a value. If your source may contain stray spaces, clean the data before counting or make a cleaned helper column. This is usually safer than quietly modifying values inside a large formula.

IFERROR is convenient when the range might have no nonblank entries, but it can also conceal other errors in the source or formula by returning 0. If the result unexpectedly shows zero, inspect the source range for errors and verify the references before treating zero as a valid count.

A small example to check the result

Suppose A2:A8 contains Apple, Pear, Apple, Banana, a blank cell, Pear, and Banana. The first formula returns 3: Apple, Pear, and Banana are the three different nonblank values. It does not return 7 for the number of rows, 4 for the number of occupied cells, or 0 because a blank is present.

This example also shows why counting the spilled results is not the same as counting the source rows. The formula creates a temporary distinct list, then counts the rows in that list. The source data remains unchanged. If you want to see which values were counted, enter =UNIQUE(FILTER(A2:A8,A2:A8<>"")) in an empty area. With the same example data in A2:A8, that formula spills Apple, Pear, and Banana into three cells.

That second formula is for displaying the list, not necessary for getting the number. Leave enough empty cells below the formula so the results can spill; otherwise Excel may show a #SPILL! error. The count formula returns a single number and usually avoids that particular issue.

Count distinct values that meet a condition

You can add a condition when the values to count are in one column and a category is in another. For example, suppose product names are in A2:A100 and regions are in B2:B100. To count distinct, nonblank products assigned to the West region, use:

With product names in A2:A100 and regions in B2:B100, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(B2:B100="West")*(A2:A100<>"")))),0). The result is the number of different nonblank products whose region is West.

In the filter condition, multiplication acts like AND: a row must have West in column B and a nonblank product in column A to be included. Replace West with the category you need, or reference a cell that contains the category so you can change it without editing the formula. Keep the two ranges the same height; mismatched range sizes can cause a calculation error.

This formula evaluates the condition from the cell values, regardless of whether rows are hidden manually or excluded by a table’s filter. In other words, applying a visible filter to the worksheet does not automatically make this formula count only visible rows. If the request is specifically for a distinct count of visible records, use a method designed to account for the filter, such as a PivotTable setup or a separate visible-row helper calculation.

Distinct count versus values appearing once

A distinct count includes every different value in the range, even if that value appears many times. A count of values appearing exactly once excludes repeated values altogether. These are different questions, and the answers can be far apart. For Apple, Pear, Apple, and Banana, the distinct count is 3, while the number of values that appear exactly once is 2: Pear and Banana.

Excel’s UNIQUE function has an optional exactly_once argument for returning only entries that occur once. When that option is TRUE, repeated values disappear from its results. You can count those results instead of the default distinct list. For A2:A100, a formula for the number of nonblank entries that occur exactly once is =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""),FALSE,TRUE)),0). The second argument tells UNIQUE to compare rows rather than columns; the third requests values occurring exactly once.

Use that version only when “appears one time” is genuinely the goal, such as identifying one-off codes. For a normal unique count, leave the exactly-once option off, as in the main formula.

Other ways to get a distinct count

If you want the distinct values themselves as a visible list, use UNIQUE rather than counting its rows. This is useful for creating a clean category list, checking spelling variations, or building a summary. Remember that the output is a dynamic array: it can grow or shrink as the source data changes, and cells in its spill area need to be empty.

On Windows desktop Excel, a PivotTable can also provide a distinct count in versions that support the Data Model. Create the PivotTable through Insert > PivotTable, select the option to add the data to the Data Model, then place the field to count in Values. Open Value Field Settings and choose Distinct Count. Availability depends on the Excel edition and how the PivotTable is created; it may not be offered in the same way on Mac or Excel for the web. A PivotTable can be useful for summarizing several categories at once, while the formula is often faster for one simple result.

Do not choose Data > Remove Duplicates just to obtain a count unless you intend to change the source list. That command removes repeated rows from the selected data; it is a data-cleaning action, not a formula that reports a count while preserving the original entries. Make a copy first if you need to use it, and check which columns are selected so Excel compares the fields you actually mean to compare.

Common counting problems and checks

  • The number seems too high: Look for leading or trailing spaces, nonprinting characters, or values that look alike but are stored differently. For example, a number stored as text may not behave like the same number stored numerically.
  • The number seems too low: Confirm that the formula covers the full data range and that the relevant rows meet any filter condition. Check for spelling differences if the entries are text.
  • A formula returns an error: Check for source errors, unequal range sizes in a conditional formula, or a blocked spill range if you are displaying the unique list.
  • Uppercase and lowercase are treated as the same: Excel’s standard comparisons in these functions are not case-sensitive, so Apple and APPLE are generally treated as the same text value. A case-sensitive count needs a different approach.
  • Hidden rows still affect the answer: The basic formula counts values in its referenced range; it does not limit the count to rows currently visible after hiding or filtering.

Some differences are not obvious at a glance. A trailing space in “Pear ” makes the text different from “Pear” for an exact distinct count, even though they look almost identical in a narrow cell. When the answer is used for reporting, clean and standardize the source data before deciding that the formula is wrong.

Frequently Asked Questions

How do I count unique values in Excel and ignore blanks?
With the values in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). FILTER removes empty cells before UNIQUE and ROWS count the remaining distinct results.

Does Excel count duplicates once or count every occurrence?
The distinct-count formula counts each different value once, regardless of how many times it occurs. Use COUNTA alone when you want the number of populated cells, including duplicates.

Can I count unique values in Excel 2016 or 2019?
Those versions do not include the modern UNIQUE and FILTER functions. A PivotTable with a Data Model may offer Distinct Count in supported Windows desktop editions; otherwise, use a compatible legacy formula or an updated Excel version.

Why does my unique count include a value that looks blank?
The cell may contain spaces, a nonprinting character, or a formula that returns something other than an empty string. The main formula excludes empty cells and empty-string results, but it does not remove spaces or clean other text.

How do I count values that appear only once?
Use UNIQUE with its exactly-once argument set to TRUE, then count the returned rows. That is a singleton count, not the usual count of all distinct values.

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.