Counting how many unique items are in a list is one of those Excel jobs that sounds simple until you sit down to do it. You scroll through the column, your eyes glaze over, and somewhere around row 200 you start wondering whether there's a formula that just gives you the answer. There is. In fact there are several, and the right one depends on which version of Excel you're running and how clean your data is.

This guide walks through every realistic way to count distinct values in Excel, from the classic SUMPRODUCT trick that's worked since Excel 2003, through the modern UNIQUE and COUNTA combo in Excel 365, to PivotTables and Power Query for larger datasets. You'll see when each method shines, where each one breaks, and how to handle the awkward edge cases (blanks, errors, case-sensitivity, multiple criteria) that tend to bite you the second the data isn't textbook clean.

If you're new to Excel formulas, don't worry: every example below starts from a concrete dataset and shows the actual cells, the formula, and what the result looks like. By the end you'll have four or five different techniques in your back pocket and a clear sense of which to reach for in any given situation.

One quick clarification before we dive in. Excel uses the word "unique" in two slightly different ways. Distinct counting means each different value is counted once, even if it appears multiple times. Unique in the stricter sense means values that appear exactly once. Most of the time when someone says "count unique values" they actually mean distinct, and that's how we'll use the terms in most of this article, but we'll show you both where it matters.

Why Excel Doesn't Have a Single COUNTDISTINCT Function

You'd think Microsoft would have added a COUNTDISTINCT function years ago. SQL has it. Power BI has it. Google Sheets has COUNTUNIQUE. Excel, perversely, makes you build the equivalent yourself unless you're on a recent version. The reason is partly historical: Excel's function library grew organically over decades and counting unique values was always solvable through array formula tricks, so a dedicated function never made the cut.

That changed in 2018 when Microsoft rolled out dynamic arrays to Excel 365 and added the UNIQUE function. Combined with COUNTA, it gave you a clean, readable one-line solution. But if you're on Excel 2019, 2016, or older versions, you're still living with the workarounds, and even on Excel 365 some scenarios are better handled by a PivotTable or Power Query rather than a formula.

The right method depends on three things: your Excel version, the size of the dataset, and whether the result needs to be dynamic (recalculating when the data changes) or one-off. Let's walk through each option in turn so you can pick the one that fits.

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

The UNIQUE function takes a range and returns a dynamic array of distinct values. By default it treats the range as a single column and returns each unique entry exactly once. Pair it with COUNTA and you have a fast, readable distinct count.

Imagine column A has a list of 200 customer names, with many repeats. The formula =COUNTA(UNIQUE(A2:A201)) returns the number of distinct customers in that list. If your range includes a header, point to A2:A201 rather than A1:A201 so the header isn't counted as a value. If the range has blank cells, UNIQUE will include the blanks as one of the distinct entries (Excel considers blank a value), so wrap with FILTER if you want to exclude them.

UNIQUE also has two optional arguments. The second argument controls whether you compare by row or column. The third argument switches between distinct mode (each value appears once, the default) and strict-unique mode (only values that appear exactly once are returned). For example =COUNTA(UNIQUE(A2:A201,FALSE,TRUE)) tells you how many values appeared exactly one time in the column. That's the answer when someone asks for "truly unique" values rather than distinct values.

Case sensitivity is where this method has a small quirk. UNIQUE is case-insensitive by default, so "Apple" and "apple" count as the same value. There's no built-in case-sensitive switch. If you need case-sensitive counting, you'll need to combine it with EXACT and a small helper formula, or fall back to Power Query.

Method 2: SUMPRODUCT and COUNTIF for Older Excel

If you're stuck on Excel 2019, 2016, or earlier, the workhorse formula is =SUMPRODUCT(1/COUNTIF(A2:A201,A2:A201)). It's a clever trick: COUNTIF returns the number of times each value appears in the range, so a value that appears three times returns 3 in three places. Take the reciprocal (1 divided by the count) and each value's contribution sums to exactly 1 regardless of how many times it appears. SUMPRODUCT adds those reciprocals up and gives you the count of distinct values.

The formula works in every modern Excel version without requiring Ctrl+Shift+Enter array entry, which is part of why it became the go-to solution. It does have one famous weakness: if any cell in the range is blank, COUNTIF returns 0 for that row, you get a division-by-zero error, and the whole formula returns #DIV/0!. The fix is to wrap with IFERROR or add a guard: =SUMPRODUCT((A2:A201<>"")/COUNTIF(A2:A201,A2:A201&"")). The &"" trick coerces empty cells to empty strings, which COUNTIF handles cleanly.

Performance is the other consideration. On 1,000 rows this formula recalculates instantly. On 50,000 rows it noticeably slows the workbook. On 200,000 rows it becomes painful. SUMPRODUCT with COUNTIF is effectively O(n^2) because each row gets compared against every other row. If your dataset is large, switch to a PivotTable or Power Query rather than fighting the formula.

Method 3: PivotTable Distinct Count

For datasets over a few thousand rows, PivotTables outperform formulas dramatically. The distinct count summary in a PivotTable runs in milliseconds even on hundreds of thousands of rows because Excel uses its internal data model engine rather than recalculating cell-by-cell. It also auto-updates when you refresh, which matters when the source data is volatile.

Here's the workflow. Select your data range, then go to Insert > PivotTable. In the Create PivotTable dialog, make sure Add this data to the Data Model is checked. This is the critical step. Without the Data Model, your only summary options are Sum, Count, Average, and a few others. With the Data Model, Distinct Count becomes available.

Once the PivotTable is created, drag your category column (let's say Region) to Rows and the column you want to count distinctly (let's say Customer Name) to Values. By default it will show a count, which is the total number of rows. Right-click the value, choose Value Field Settings, scroll to the bottom and pick Distinct Count. The numbers update immediately and you now have distinct customers per region.

The biggest win with PivotTables is exploration. Want to slice by month? Drag Date to Columns. Want to filter by product category? Add a Slicer. The distinct count recalculates instantly for every slice, something that would require dozens of separate formulas to replicate with SUMPRODUCT.

Method 4: Power Query for Large Datasets

Once your data exceeds a few hundred thousand rows or you need to combine multiple sources, Power Query becomes the right tool. It loads data in chunks, processes transformations server-side where possible, and handles distinct counting at scale better than any cell formula ever could.

The setup is straightforward. Select your table (or just the data range) and click Data > From Table/Range. The Power Query Editor opens with your data preview. From the Home tab choose Group By. Pick the column you want to count distinct values of, name the new column something like "Distinct Customers," and in the Operation dropdown select Count Distinct Rows. Click OK, then Close & Load.

The result is a clean table with your grouping column and the distinct count. The query is reusable: any time the source data updates, right-click the query and choose Refresh. Power Query also gives you control over edge cases that formulas struggle with. You can configure whether nulls are counted, whether trimming and case-folding happen before the distinct count, and whether to group by multiple columns simultaneously.

One subtle advantage: Power Query is case-sensitive by default in its Group By distinct count. So "Apple" and "apple" are treated as separate values, the opposite of what UNIQUE does. If you want case-insensitive behaviour, transform the column with Lowercase before grouping. This level of explicit control is part of why analysts working with large or messy data prefer Power Query over cell formulas for production work.

Method 5: FREQUENCY for High-Performance Numeric Counts

For purely numeric distinct counts on large ranges in older Excel versions, the FREQUENCY function combined with SUMPRODUCT outperforms the SUMPRODUCT/COUNTIF approach significantly. The formula is =SUMPRODUCT(--(FREQUENCY(A2:A1001,A2:A1001)>0)). FREQUENCY returns an array showing how many times each value appears within bins defined by the same range, and the boolean coercion counts each bin that has at least one occurrence.

This is fast because FREQUENCY is internally optimised in Excel's calculation engine. On 100,000 numeric values it returns in well under a second, whereas the COUNTIF approach can take 30 seconds or more. The catch is that FREQUENCY only works on numeric data; text values are ignored. For text-heavy columns you'll need MATCH to convert each text value to its first occurrence position, then run FREQUENCY on those positions: =SUMPRODUCT((A2:A1001<>"")/COUNTIF(A2:A1001,A2:A1001&"")) for text, or =SUMPRODUCT(--(FREQUENCY(MATCH(A2:A1001,A2:A1001,0),ROW(A2:A1001)-ROW(A2)+1)>0)) for the fast text version.

That second formula is notorious for being unreadable. It's the kind of thing you copy-paste from a reference, comment carefully, and never refactor unless you have to. If readability matters more than performance, stick with COUNTA(UNIQUE()) in Excel 365 or just use a PivotTable.

Real-World Example: Counting Distinct Customers per Sales Rep

Let's run a concrete example. You have a sales transactions table with three columns: Sales Rep in column A, Customer in column B, and Order Value in column C. The table has 5,000 rows, one transaction per row. You want to know how many distinct customers each rep has worked with.

The Excel 365 solution is two cells. In column E list each rep name (or use UNIQUE(A2:A5001) to generate the list automatically). In column F next to each rep, write =COUNTA(UNIQUE(FILTER($B$2:$B$5001,$A$2:$A$5001=E2))). The FILTER grabs only the customers whose rep matches the row, UNIQUE deduplicates them, and COUNTA counts what's left. Drag the formula down and you have a complete distinct customer count per rep.

The PivotTable solution is even faster for a one-off check. Insert PivotTable with Data Model checked. Drag Sales Rep to Rows, drag Customer to Values, switch the value summary to Distinct Count. Done in 15 seconds and easy to slice by date or product if you need to.

For 100,000+ rows or recurring monthly reports, build the same calculation in Power Query. Load the table, Group By Sales Rep with Operation = Count Distinct Rows on Customer. The query becomes part of your workbook, refreshes on demand, and handles much larger datasets without bogging down the recalc engine.

Notice that all three methods produce the same answer but they suit different contexts. Formulas for small or transparent calculations, PivotTables for interactive analysis, Power Query for scale and reproducibility. Knowing all three gives you the flexibility to pick the right tool for the job in front of you.

Final Thoughts: Picking the Right Method

Counting distinct values in Excel turns out to be less about knowing one secret formula and more about matching the right tool to the situation. For ad-hoc analysis on small to medium datasets in Excel 365, COUNTA(UNIQUE(...)) is hard to beat for clarity and speed. For older versions, the classic SUMPRODUCT(1/COUNTIF(...)) still works fine on anything under 50,000 rows.

For larger or more dynamic work, lean on PivotTables. The Data Model's Distinct Count feature gives you instant answers and lets you slice the data by other dimensions without rebuilding formulas. And once you cross into hundreds of thousands of rows or recurring reporting workflows, Power Query is the right home for distinct counting because it handles the scale and offers explicit control over case sensitivity, blanks, and trimming.

Whichever method you pick, build the habit of cleaning the data first. TRIM and CLEAN on the source column will catch most of the trailing-space and hidden-character issues that silently inflate counts. Decide up front whether blanks should count, whether case matters, and whether you want "distinct" (each value at least once) or "strictly unique" (appears exactly one time). Document the choice in a comment near the formula so a future reviewer doesn't have to guess.

If you're regularly counting distinct values as part of larger analyses, it's worth investing thirty minutes to get comfortable with Power Query's Group By. It pays for itself the first time you have to redo a calculation across a refreshed dataset, and it handles edge cases that formulas struggle with. Start with one Group By query, see how it refreshes, and you'll quickly find yourself using it for other repetitive tasks as well.

One last tip worth remembering: whenever a distinct count looks suspiciously high or low, go back and inspect the underlying data with the eye-test rather than only trusting the formula. A quick sort, a filter, or pasting the column into Power Query for a glance will usually surface the formatting issue or the data quality glitch causing the discrepancy faster than any new formula attempt.