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. Excel has no COUNTUNIQUE function (that one is Google Sheets), so this pairing is the Excel equivalent.

=COUNTA(UNIQUE(A2:A100))

To count only the values that appear exactly once (true one-offs, not one of each), set UNIQUE's third argument to TRUE:

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

How do I stop COUNTA(UNIQUE()) from counting blank cells?

Filter the blanks out before UNIQUE sees them. UNIQUE treats an empty cell as a legitimate value (it spills as 0), so COUNTA(UNIQUE(A2:A100)) comes out one too high whenever the range contains a blank, which is almost always the case when you size the range larger than the data to leave room for new rows.

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

One edge case: if the whole range is blank, this returns 1 instead of 0, because FILTER has nothing left and returns #CALC!, which COUNTA counts as an item. The zero-safe pattern in the conditions section below fixes that.

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! and COUNTA counts that error as one item, so the formula shows 1 instead of 0. Wrapping it in IFERROR does not help, because COUNTA returns that 1 rather than an error. Instead, give FILTER an empty-string fallback and count only the results that are not empty:

=SUM(--(LEN(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 count unique values between two dates?

Put both date tests inside FILTER and multiply them, so a row survives only if its date is on or after the start date and on or before the end date, then count what is left with COUNTA(UNIQUE()). This example counts distinct entries in column B of a "Detailed Data" sheet whose dates in column A fall between the start and end dates typed into A9 and A10 of a "Weekly Report" sheet:

=COUNTA(UNIQUE(FILTER('Detailed Data'!B2:B5000, ('Detailed Data'!A2:A5000>='Weekly Report'!A9)*('Detailed Data'!A2:A5000<='Weekly Report'!A10))))

When this kind of formula returns an error or a wrong number, it is usually one of these:

• Operators: write >= and <=. => and =< are not Excel comparison operators.
• COUNTIFS-style criteria: FILTER takes one include argument, FILTER(array, include, [if_empty]), so you cannot list range/criteria pairs like A:A, ">="&A9. Build each test as a comparison and join them with * for AND.
• Mismatched ranges: the date range and the counted range must cover the same rows. Filtering B2:B5000 by a test on A:A returns an error.
• Extra COUNTA arguments: COUNTA(B2:B5000, UNIQUE(...)) adds every non-blank cell in column B to the result. Put only the UNIQUE(FILTER()) expression inside COUNTA.
• Dates with times: if column A holds date-times, a <= test on the end date drops everything after midnight on that day. Compare with <'Weekly Report'!A10+1 instead.

If a week can have no rows, switch to the SUM(--(LEN(...)>0)) pattern from the previous section, with "" as FILTER's third argument, so an empty week reads 0 instead of 1. Open-ended ranges such as B2:B are Google Sheets syntax; in Excel, use bounded ranges like B2:B5000 or full columns like A:A and B:B for both parts of the formula.

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.