How to Count Distinct Values in Excel

To count distinct values in Excel, use =COUNTA(UNIQUE(range)) in Microsoft 365 and Excel 2021, or =SUMPRODUCT(1/COUNTIF(range,range)) in older versions. Both return the number of different values in a range — a customer list of Anna, Bob, Anna, Carol has 3 distinct values, not 4. This guide covers every reliable method, works on Windows and Mac, and shows how to handle blanks, multiple columns and filtered data.

“Distinct” and “unique” are often used interchangeably, but they can mean slightly different things. Distinct count = how many different values exist (Anna counts once even though she appears twice). Unique count = how many values appear exactly once (only Bob and Carol, so 2). Most people searching for a distinct count want the first definition, and that is what the methods below return unless noted.

Quick comparison of methods

Method	Best for	Excel version	Result type
COUNTA(UNIQUE())	The simplest modern formula	365 / 2021+	Live, recalculates
SUMPRODUCT/COUNTIF	Older Excel, no spill needed	All versions	Live, recalculates
PivotTable Distinct Count	Large datasets, dashboards	2013+ (Windows)	Refreshable
Remove Duplicates	One-off cleanup	All versions	Static list
Power Query Group By	Repeatable data pipelines	2016+ / 365	Refreshable

Method 1: COUNTA + UNIQUE (the modern way)

If you have Microsoft 365, Excel 2021, or Excel for the web, the UNIQUE function is the cleanest option. UNIQUE returns a spilled list of every distinct value; wrapping it in COUNTA counts how many there are.

=COUNTA(UNIQUE(A2:A100))

This single formula gives you the distinct count instantly and recalculates whenever the data changes. To learn the function in depth, see our guide on how to use the UNIQUE function in Excel.

Ignore blank cells

UNIQUE treats an empty cell as a value (it appears as 0), which can inflate your count by one. To exclude blanks, filter them out first:

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

The FILTER function strips out empty cells before UNIQUE runs. If you only want values that appear exactly once (a true unique count), add the third argument: =COUNTA(UNIQUE(A2:A100, , TRUE)).

Method 2: SUMPRODUCT and COUNTIF (works in every version)

Before UNIQUE existed, the standard array formula for a distinct count was:

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

Here is the logic: COUNTIF counts how many times each value appears. If “Anna” appears 3 times, each of those three cells returns 3, and 1/3 + 1/3 + 1/3 = 1. Summing those fractions across the whole range gives the number of distinct values. This works in Excel 2010, 2013, 2016, 2019 and on the Mac without pressing Ctrl+Shift+Enter — SUMPRODUCT handles the array internally.

Important: this version breaks if the range contains any blank cells, because COUNTIF returns 0 for a blank and dividing by zero throws a #DIV/0! error. To make it blank-safe, add an empty string to the divisor:

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

For a deeper look at this function, read our SUMPRODUCT function guide. For the counting half of the formula, see how to use COUNTIF in Excel.

Method 3: Distinct count across multiple criteria

Sometimes you need a distinct count that respects conditions — for example, “how many distinct products did John sell?” Use COUNTIFS inside the same reciprocal pattern:

=SUMPRODUCT((B2:B100="John")/COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100))

In Microsoft 365 the equivalent is cleaner and easier to audit:

=COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="John")))

The FILTER step keeps only John’s rows, then UNIQUE collapses them to distinct products and COUNTA counts them.

Method 4: PivotTable with Distinct Count

For large datasets or reports you will refresh, a PivotTable is the most robust option. The Distinct Count summary is built in on Windows (Excel 2013 and later) through the Data Model.

1. Select your data and go to Insert → PivotTable.
2. In the dialog, tick Add this data to the Data Model, then click OK. This step is what unlocks Distinct Count.
3. Drag the field you want to count into the Values area.
4. Click the field in Values → Value Field Settings.
5. Scroll to the bottom of the summary list and choose Distinct Count, then click OK.

Distinct Count appears only at the bottom of the list, and only when the data was added to the Data Model — if you do not see it, that checkbox in step 2 was missed. On Excel for Mac, the classic PivotTable engine does not expose Distinct Count, so use Method 1 or 5 instead. New to pivots? Start with our complete guide to Excel pivot tables.

Method 5: Power Query Group By

Power Query (the Get & Transform tools) is ideal when the same report runs every week, because it refreshes with one click. It is available on Windows (2016+ and 365) and on recent Mac builds.

1. Select your range and choose Data → From Table/Range.
2. In the Power Query Editor, select the column you want to analyze.
3. On the Transform tab, click Group By.
4. Choose Count Distinct Rows as the operation (use Count Rows for a plain total).
5. Click Close & Load to send the result back to a worksheet.

Power Query never alters your source data, so it is the safest method for recurring distinct-count reports.

Method 6: Remove Duplicates (a quick manual count)

If you just need a one-time answer and do not need a formula, Remove Duplicates is fastest:

1. Copy the column to a blank area so you do not destroy your source data.
2. Select the copy, go to Data → Remove Duplicates.
3. Confirm the columns and click OK. Excel reports how many duplicates were removed and how many unique values remain.

The number of remaining rows is your distinct count. For more on this tool, see how to remove duplicate rows in Excel and how to find unique values in Excel. Because this method is destructive on the copied data, never run it directly on your master list.

Keyboard shortcuts that speed this up

Action	Windows	Mac
Edit the active cell / formula	F2	Ctrl + U
Confirm a legacy array formula	Ctrl + Shift + Enter	⌘ + Return or Ctrl + Shift + Return
Insert a PivotTable (after selecting data)	Alt → N → V	n/a (use the ribbon)
Open the Filter dropdown	Alt + ↓	Option + ↓

If you are on Microsoft 365, modern dynamic-array formulas like UNIQUE and FILTER spill automatically — you press Enter normally and never need Ctrl+Shift+Enter. The legacy SUMPRODUCT formula also confirms with a plain Enter.

Troubleshooting common problems

• Count is one too high. A blank cell is being counted as a distinct value. Use the blank-safe formulas in Methods 1 and 2.
• #DIV/0! error. Your range contains blanks and you used the plain SUMPRODUCT(1/COUNTIF(...)). Switch to the &"" version above.
• #SPILL! error. UNIQUE cannot spill because cells below the formula are not empty. Clear the cells in the spill range.
• #NAME? error. UNIQUE, FILTER, or COUNTIFS is not available in your Excel version. Use the SUMPRODUCT method instead.
• Distinct Count is missing from the PivotTable. The data was not added to the Data Model — rebuild the pivot and tick that box, or use a formula method.
• Trailing spaces split values. “Anna” and “Anna ” count as two distinct values. Wrap the range in TRIM first, or clean it with Excel’s text functions.

Distinct count vs. a plain count

A regular count answers a different question. If you simply want the total number of populated or numeric cells, use COUNT and COUNTA rather than the distinct methods here. For counting specific things, we also cover counting names in Excel and counting blank cells.

Frequently Asked Questions

How do I count distinct values in Excel?

Use =COUNTA(UNIQUE(range)) in Microsoft 365 or Excel 2021. In older versions use =SUMPRODUCT(1/COUNTIF(range,range)). Both return the number of different values in your range. To ignore blank cells, use =SUMPRODUCT((range<>"")/COUNTIF(range,range&"")).

What is the difference between distinct count and unique count?

A distinct count counts each different value once — if “Anna” appears three times, she counts once. A unique count counts only values that appear exactly one time, so “Anna” would not be counted at all. For a true unique count in Microsoft 365, use =COUNTA(UNIQUE(range, , TRUE)).

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

When creating the PivotTable, tick Add this data to the Data Model. Then add your field to the Values area, open Value Field Settings, and choose Distinct Count from the bottom of the summary list. This option is available on Excel 2013+ for Windows; Mac users should use a formula instead.

How do I count unique values while ignoring blanks?

Wrap your range in FILTER before UNIQUE: =COUNTA(UNIQUE(FILTER(range, range<>""))). In older Excel, use the blank-safe SUMPRODUCT formula: =SUMPRODUCT((range<>"")/COUNTIF(range, range&"")), which avoids the #DIV/0! error blanks would otherwise cause.

Can I count distinct values based on a condition?

Yes. In Microsoft 365 use =COUNTA(UNIQUE(FILTER(values, criteria_range=criteria))). In older versions use =SUMPRODUCT((criteria_range=criteria)/COUNTIFS(values, values, criteria_range, criteria_range)) to count distinct values that meet your condition.

Why does my distinct count look one too high?

Excel is almost always counting an empty cell as a value. Use a blank-aware formula such as =COUNTA(UNIQUE(FILTER(range, range<>""))), or check for hidden trailing spaces that make otherwise-identical entries look different.

Summary

For a quick, accurate distinct count, reach for =COUNTA(UNIQUE(range)) in modern Excel and =SUMPRODUCT(1/COUNTIF(range,range)) everywhere else. Use a PivotTable’s Distinct Count for dashboards, Power Query for recurring reports, and Remove Duplicates for one-off cleanups. Handle blanks and trailing spaces up front and your numbers will stay reliable across every Excel version, on both Windows and Mac.