AI Excel Generator
← ALL GUIDES

How to Count Unique Values in Excel

Updated November 4, 2026 · AI Excel Generator

To count unique values in Excel, first decide whether you mean values that appear at least once or values that appear exactly once.

The right formula depends on that distinction, as well as your Excel version and whether blanks should count.

Unique values versus values that appear once

These terms are easy to mix up.

A distinct value is counted once even if it occurs repeatedly.

An exactly-once value is one whose total frequency in the range is one.

For example, if A2:A8 contains Apple, Pear, Apple, Plum, Pear, Kiwi, and Apple, there are four distinct values: Apple, Pear, Plum, and Kiwi.

Only Plum and Kiwi appear exactly once, so there are two exactly-once values.

Count distinct values in Excel 365 or Excel 2021

For a range of numbers or text with no blanks to exclude, use COUNTA with UNIQUE:

=COUNTA(UNIQUE(A2:A100))

UNIQUE returns the distinct entries in the range, and COUNTA counts them.

For the example range above, the result is 4.

This formula is available in Microsoft 365 and Excel 2021 or later, including supported Excel for the web versions.

If the range may contain blank cells and you do not want blanks included, use FILTER first:

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

The comparison operator <> means “not equal to.” Here, FILTER keeps only nonblank cells before UNIQUE creates the distinct list.

In the filtered example, blank cells do not add to the count.

To count distinct values that meet a condition, put the test inside FILTER.

For example, if categories are in B2:B100 and you want distinct values from column A where the category is Fruit, use:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(B2:B100="Fruit")*(A2:A100<>"")))),0)

Make sure both ranges have the same number of rows. This formula excludes blank values and returns zero when no rows match. IFERROR also hides other calculation errors, so check the source range for errors separately before relying on the count.

Count values that appear exactly once

To count entries whose frequency in the range is one, use UNIQUE with its exactly_once argument set to TRUE:

=IFERROR(ROWS(UNIQUE(A2:A8,,TRUE)),0)

For the example list, this returns 2, counting Plum and Kiwi.

To exclude blanks as well, filter the input first:

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

In UNIQUE(array,[by_col],[exactly_once]), the second argument is omitted here so the third argument can be set to TRUE.

This formula also requires Excel 365 or Excel 2021 and later.

Count distinct numbers or text in older Excel

In Excel versions without UNIQUE and FILTER, Advanced Filter offers a formula-free method. Copy your source column, including its header, to a separate working sheet. Select it, open Data → Advanced, choose Copy to another location, and enable Unique records only.

Copy the unique results to a clean column, remove any blank entry you do not want counted, then use COUNTA on just the resulting data cells, excluding the header. Keep this extracted list separate from the original records; rerun the extraction when the source changes.

Check the result against a small sample when using older formulas, particularly if cells contain errors or the range mixes data types.

Legacy array formulas can be slower on large ranges, so keep the referenced range as small as practical.

Count distinct values with a condition in older Excel

First filter the source table to the desired category, such as Fruit. Copy only the visible matching values and their header to a separate working sheet, leaving the original data unchanged. Then use Advanced Filter with Unique records only on that copied list, remove any blank result, and count the extracted data cells without the header.

Common issues that change the count

  • Blanks: Decide whether empty cells should count.

    Use FILTER(range,range<>"") to remove them before counting distinct values.
  • Whitespace: Apple and Apple may be treated as different entries because the second contains a trailing space.

    Clean imported text with TRIM when appropriate.
  • Numbers stored as text: A numeric value and a text value that looks like a number may not behave the way you expect in comparisons.

    Standardize the column before counting if the source combines types.
  • Errors: An error value in the input can propagate through UNIQUE or a legacy formula.

    Correct the source error or handle it explicitly before calculating.
  • Case: Standard Excel comparisons and UNIQUE treat text case-insensitively for this purpose, so entries such as apple and Apple generally count as the same value.

Which formula should you use?

  • For the supplied seven-item sample: =COUNTA(UNIQUE(A2:A8)) returns 4.
  • For distinct nonblank values: =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0).
  • For nonblank values appearing exactly once: =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""),,TRUE)),0).
  • In older Excel, extract a unique list with Advanced Filter, then count its data cells. Refresh that extracted list when the source changes.