Count Unique Cells in Excel: Formulas and Examples

Formulas & functions · Updated November 22, 2026

To count distinct, nonblank values in Excel for Microsoft 365, use =COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>""))). This assumes the values are in A2:A10; if the range contains Apple, pear, Apple, a blank, PEAR, plum, 0, a blank, and plum, the formula returns 4 because Excel treats Apple/Apple, pear/PEAR, plum/plum, and zero as four distinct values.

What “unique cells” means in Excel

People often use “count unique cells” to mean counting how many different values appear in a range. That is different from counting all populated cells: if a list has 100 entries but only 20 different names, the distinct-value count is 20. It is also different from counting values that appear exactly once. A value repeated several times still contributes one to a distinct count.

Decide what should count before choosing a formula. For example, are blank cells excluded? Should zero count? Are two rows considered duplicates only when every column matches, or when one particular field matches? These choices can change the answer, even when the worksheet itself has not changed.

Use UNIQUE and FILTER for a distinct count

In Microsoft 365 and Excel 2021, UNIQUE returns the different items in a range, while FILTER can remove blanks before the count. COUNTA then counts the remaining results. This is generally the clearest method for a single column of data, and it updates when source values change.

For the A2:A10 example in the opening, =COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>""))) returns 4. The FILTER test excludes empty cells, UNIQUE reduces repeated values to one of each, and COUNTA counts the resulting list. The value 0 is not blank, so it is included. A formula returning an empty string is also excluded by the test A2:A10<>””.

If every cell in the range is blank, FILTER has no matching rows and normally returns an error. Wrap the calculation in IFERROR if you want zero instead. With the same A2:A10 layout, =IFERROR(COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>""))),0) returns 0 when there are no nonblank values; for the example list with four distinct values, it returns 4.

UNIQUE is a dynamic-array function. Enter the formula in one cell; Excel calculates the result there rather than spilling a list, because COUNTA is counting the UNIQUE results internally. If you instead enter =UNIQUE(FILTER(A2:A10,A2:A10<>"")) in a cell, Excel spills the distinct list into neighboring cells. Make sure those cells are clear, or Excel may show a #SPILL! error.

Count distinct values that meet a condition

To count different items only when another column meets a condition, combine FILTER with a Boolean test. Suppose product names are in A2:A8 and regions are in B2:B8. If the rows are Apple/East, pear/West, apple/East, plum/East, pear/West, plum/West, and banana/West, the following formula returns 2: =IFERROR(COUNTA(UNIQUE(FILTER(A2:A8,(A2:A8<>"")*(B2:B8="East")))),0). In that layout, the distinct East-region products are Apple and plum.

The asterisk between the tests means both conditions must be true: the product is not blank and the region is East. Replace East with another text criterion, or refer to a cell containing the criterion, such as D1. If the criterion matches no rows, IFERROR makes the answer zero rather than displaying a no-results error. Check that the two ranges begin and end on the same rows; mismatched array sizes can cause errors.

Count unique combinations across columns

Sometimes uniqueness belongs to a whole record, not one column. For example, “Red, Small” and “Red, Large” are different combinations even though both rows have the same color. UNIQUE can compare rows across multiple columns, and ROWS can count how many distinct rows it returns.

Assume A2:B6 contains these five color-and-size pairs: Red/Small, Blue/Large, Red/Small, Green/Medium, and Blue/Large. The formula =ROWS(UNIQUE(A2:B6)) returns 3, because there are three distinct pairs. This counts exact row combinations across the selected columns. If your data has fully blank rows that should not count, filter them out first using a column that must be populated; otherwise a blank row can itself be treated as a distinct combination.

For a larger table, select only the columns that define identity. Including a date, note, or transaction number can make otherwise similar records count as different. Conversely, leaving out a field that matters can merge records that should stay separate.

Older Excel options without UNIQUE

Excel versions without UNIQUE can use SUMPRODUCT and COUNTIF for a single-column list. With the same A2:A10 example from above, =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")) returns 4. The formula gives each occurrence a fraction of one based on how many times its value appears, then sums those fractions; the blank check prevents empty cells from contributing.

This older formula is less transparent and can be less suitable for unusual data. COUNTIF treats text criteria without regard to letter case, and characters such as asterisk and question mark have wildcard meaning in criteria. If values contain those characters, or the range includes errors, inspect the result rather than assuming the formula handles every case. For old Excel versions, another option is a PivotTable with Distinct Count, described below.

There is also a related but different question: how many entries occur exactly once? With values cat, dog, cat, fish, bird, bird, and owl in A2:A8, =SUMPRODUCT((A2:A8<>"")*(COUNTIF(A2:A8,A2:A8)=1)) returns 3: dog, fish, and owl each occur once. Do not use this result as the count of all distinct values; cat and bird are distinct values too, but they occur more than once.

Count distinct values with a PivotTable

A PivotTable is useful when you want a count alongside other summaries, or when you prefer a visible report over a worksheet formula. On Windows, select the data and choose Insert > PivotTable. In the Create PivotTable dialog, select Add this data to the Data Model, create the PivotTable, and place the field you want to count in the Values area. Open Value Field Settings for that field and choose Distinct Count under Summarize Values By.

Distinct Count is available for a PivotTable built using the Data Model; if you do not select that option when creating it, the setting may not appear. A PivotTable is not automatically refreshed every time source cells change. Use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh, after updating the source data. Excel for Mac has PivotTable features, but the available commands and dialog layout can vary by version; if the Data Model or Distinct Count option is unavailable, use a formula or Power Query instead.

Common counting problems and checks

  • Blanks are counted unexpectedly: use FILTER with the nonblank test shown above. A range with genuinely empty cells and cells containing formulas that return an empty string can behave differently in some counting methods.
  • Zero disappears: zero is a value, not a blank. The nonblank test keeps it. If zero should be excluded too, add a condition such as A2:A10<>0 to the FILTER criteria.
  • Similar text is treated as different: leading or trailing spaces can make entries such as “West” and “West ” appear separate. Clean imported data where appropriate, for example with TRIM in a helper column, and then count the cleaned values.
  • Numbers and numbers stored as text are mixed: a numeric 12 and text “12” may not be treated as the same underlying value. Check cell formats and imported data types if the result seems too high.
  • The formula shows an error: confirm the function is supported in your Excel version, check for errors in source cells, and make sure spilled results have room if you entered UNIQUE by itself.

Excel’s ordinary text comparisons are generally not case-sensitive, so Apple and APPLE are normally treated as the same value by these approaches. If case itself defines uniqueness, standard UNIQUE and COUNTIF formulas do not provide a case-sensitive distinct count by default; the data needs a case-aware method rather than a small adjustment to the basic formula.

Choose the right method for your workbook

  • Use UNIQUE with FILTER for a live count of distinct nonblank values in Microsoft 365 or Excel 2021.
  • Use a conditional FILTER formula when the count should reflect a region, status, date range, or other criterion.
  • Use ROWS with UNIQUE when a distinct item is a combination of fields in the same row.
  • Use a PivotTable Distinct Count when you need a report or summary and can add the data to the Data Model.
  • Use a legacy formula only when newer functions are unavailable, and verify edge cases such as wildcard characters and errors.

For a list that grows over time, consider converting the source range to an Excel Table with Insert > Table. Structured references can make formulas easier to read and help the referenced data expand when rows are added. For example, if a table is named Sales and its column is Product, a dynamic-array formula can use that column instead of a fixed A2:A10 range; make sure the table column contains the intended values and account for blanks in the same way.

Frequently Asked Questions

Does UNIQUE count blanks?
UNIQUE can return a blank item when blanks are present in the source. To count only nonblank distinct values, filter the range first with a condition such as A2:A10<>””, then count the UNIQUE results.

How do I count distinct values in Excel without UNIQUE?
In older Excel, a SUMPRODUCT/COUNTIF formula can count distinct nonblank values in a single column. It is more sensitive to edge cases than the modern formula, so check for wildcard characters, errors, and inconsistent data types.

Does Excel consider uppercase and lowercase different?
Not with the standard distinct-count formulas described here. Excel’s usual text comparisons are case-insensitive, so values that differ only by capitalization are generally treated as the same.

How do I count unique values based on another column?
Use FILTER to keep rows matching the condition, then pass those results to UNIQUE and count them. If no rows match, wrap the calculation in IFERROR to return zero.

What is the difference between distinct and exactly-once values?
A distinct count includes every different value once, regardless of how often it appears. An exactly-once count includes only values whose frequency in the range is one; repeated values are excluded.

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.