Count Unique Values in Excel: 4 Ways That Actually Work

The fastest way to count unique values in Excel is =COUNTA(UNIQUE(range)) — but that only works in Excel 365 and Excel 2021. If you are on an older version, need to count uniques that meet a condition, or want the count inside a pivot table, you need a different technique. Here are all four, with the gotchas (especially blanks) that make each one return the wrong number.

What is the modern formula for counting unique values?

Wrap the UNIQUE function in COUNTA. UNIQUE spills the list of distinct entries, and COUNTA counts how many items are in that spilled list. It works for text, numbers, and mixed data, and it recalculates automatically as your data changes.

=COUNTA(UNIQUE(A2:A100))

Two useful variations:

' Count values that appear EXACTLY ONCE (truly one-off values)
=COUNTA(UNIQUE(A2:A100, FALSE, TRUE))

' Ignore blanks in the range
=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

The second variation matters because UNIQUE treats a blank cell as a legitimate value (it returns 0 for it), which silently inflates your count by one.

How do I count unique values in older Excel versions?

Use the classic SUMPRODUCT/COUNTIF trick. COUNTIF counts how many times each value appears; dividing 1 by that count gives each occurrence a fractional share (a value appearing 3 times contributes 1/3 + 1/3 + 1/3 = 1); SUMPRODUCT adds the shares, so every distinct value contributes exactly 1 to the total.

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

Warning: if the range contains any blank cell, COUNTIF returns 0 for it and the formula throws #DIV/0!. The blank-safe version is:

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

Appending &"" forces COUNTIF to treat everything as text (so blanks count as empty strings, never zero), and the (A2:A100<>"") numerator zeroes out the blanks' contribution. This formula is slow on very large ranges — COUNTIF runs once per cell — so keep it under a few thousand rows or switch to a pivot table.

How do I count unique values with a condition?

Combine UNIQUE with FILTER: filter the rows first, then count the distinct values that survive. This is the Excel equivalent of SQL's COUNT(DISTINCT ...) WHERE ... — the same pattern covered in our SQL distinct count tip.

' Unique customers in the "West" region
=COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="West")))

' Unique customers in West with sales over 500
=COUNTA(UNIQUE(FILTER(A2:A100, (B2:B100="West")*(C2:C100>500))))

If no rows match, FILTER returns #CALC!. Guard it with IFERROR so an empty result reads as zero:

=IFERROR(COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="West"))), 0)

For pre-365 Excel, the conditional version uses the same SUMPRODUCT idea with COUNTIFS, but it gets fragile fast — this is the point where upgrading or using a pivot table is genuinely the better answer.

How do I get a distinct count in a pivot table?

Pivot tables can show Distinct Count natively, but only when the data is loaded into the Data Model. When you create the pivot (Insert → PivotTable), tick the "Add this data to the Data Model" checkbox — that single click unlocks the option. Then:

1. Insert → PivotTable → tick "Add this data to the Data Model" → OK
2. Drag your category field (e.g. Region) to Rows
3. Drag the field to count (e.g. Customer) to Values
4. Right-click the value → Value Field Settings
5. Scroll to the bottom of the list → choose "Distinct Count"

If "Distinct Count" is missing from the list, the data was not added to the Data Model — rebuild the pivot with the checkbox ticked. Data Model pivots count a blank cell as one distinct "(blank)" value, so filter blanks out of the pivot if you do not want them included.

Which method should I use?

Use COUNTA(UNIQUE()) whenever you have Excel 365/2021 — it is the fastest to write, easiest to read, and easiest to extend with FILTER conditions. Use the pivot table Distinct Count when you need unique counts per group (per region, per month) rather than one number. Reserve SUMPRODUCT/COUNTIF for older Excel versions, and remember its blank-cell trap.