FormulaFix · Plain-English guides to Excel and Google Sheets formulas

How to Count Unique Values in Excel: 5 Methods That Work

"Unique" can mean two different things, so pick the right one first:

Term Meaning Example list: Apple, Banana, Apple, Cherry
Distinct values Every different value, counted once 3 (Apple, Banana, Cherry)
Values that occur once Items with no duplicate at all 2 (Banana, Cherry)

All examples below use this list in A2:A11:

A (Fruit)
Apple
Banana
Apple
Cherry
Banana
Date
Apple
Cherry
Fig
Date

Distinct values: Apple, Banana, Cherry, Date, Fig = 5. Values that occur exactly once: Fig = 1.

Method 1: UNIQUE + COUNTA (Excel 365/2021)

=COUNTA(UNIQUE(A2:A11))

Result: 5

If the range may contain blank cells, filter them out first, otherwise a blank counts as one extra value:

=COUNTA(UNIQUE(FILTER(A2:A11, A2:A11<>"")))

Result: 5

Count only the values that occur exactly once:

=COUNTA(UNIQUE(A2:A11, FALSE, TRUE))

Result: 1 (only Fig). The third argument TRUE means "return values that appear exactly once".

Method 2: SUMPRODUCT (works in every Excel version)

=SUMPRODUCT(1/COUNTIF(A2:A11, A2:A11))

Result: 5

Each item contributes 1 / (how many times it appears), so Apple adds 3 times 1/3 = 1.

Warning: this formula returns #DIV/0! if the range contains blank cells. For a range that may have blanks, use:

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

Method 3: Unique count with a condition

Suppose column B holds the Region for each fruit and you want the number of distinct fruits sold in "East" (Excel 365/2021):

=COUNTA(UNIQUE(FILTER(A2:A11, B2:B11="East")))

FILTER keeps only the East rows, UNIQUE removes repeats, and COUNTA counts what is left. If no row matches, FILTER returns #CALC!, so wrap the whole formula as =IFERROR(COUNTA(UNIQUE(FILTER(A2:A11, B2:B11="East"))), 0) to show 0 instead.

Method 4: PivotTable Distinct Count

A normal PivotTable cannot count distinct values. You must add the data to the Data Model:

  1. Click inside your data and choose Insert > PivotTable.
  2. Tick Add this data to the Data Model and click OK.
  3. Drag Fruit to Values.
  4. Click the field in Values, choose Value Field Settings, scroll to the bottom of the list and pick Distinct Count.

Result: 5. Refresh the PivotTable (Alt + F5) after the data changes.

Method 5: Power Query

  1. Select your data and choose Data > From Table/Range.
  2. In the editor, right-click the Fruit column and choose Remove Duplicates.
  3. Choose Transform > Count Rows.
  4. Click Close & Load.

Result: a single cell with 5.

Google Sheets

=COUNTUNIQUE(A2:A11)

Result: 5. COUNTUNIQUE exists only in Google Sheets, not in Excel. It ignores blank cells.

Across two columns: =COUNTUNIQUE(A2:A11, C2:C11) counts distinct values in the two ranges combined.

Common problems

Problem Cause Fix
#NAME? on UNIQUE Excel 2019 or older Use the SUMPRODUCT method
#DIV/0! in SUMPRODUCT Blank cells in the range Use the version with (range<>"") shown above
Count is one too high UNIQUE returns a blank as a value Wrap with FILTER(range, range<>"")
"apple" and "Apple" count as one Excel text comparison ignores case Use a helper column with EXACT if case matters
Extra spaces make duplicates "Apple" and "Apple " are different text Clean with TRIM() first
Distinct Count missing in PivotTable Data was not added to the Data Model Recreate the PivotTable with the Data Model option ticked

FAQ

How do I count unique values in a column in Excel?

In Excel 365 or 2021 use =COUNTA(UNIQUE(A2:A11)). In any older version use =SUMPRODUCT(1/COUNTIF(A2:A11, A2:A11)), as long as the range has no blank cells.

How do I count unique values across two columns?

In Excel 365 use =COUNTA(UNIQUE(VSTACK(A2:A100, C2:C100))). In Google Sheets use =COUNTUNIQUE(A2:A100, C2:C100).

What is the difference between unique and distinct?

In Excel's UNIQUE function, "unique" returns every different value once (distinct). Only when you set the third argument to TRUE does it return values that appear exactly once.

Does Excel have a COUNTUNIQUE function?

No. COUNTUNIQUE is Google Sheets only. In Excel combine COUNTA and UNIQUE, or use SUMPRODUCT and COUNTIF.

Related guides