The short answer: to get a distinct count in an Excel pivot table, tick Add this data to the Data Model when you create the pivot, then set the value field to Distinct Count, which sits at the bottom of the Summarize Values By list. If that option is missing, the pivot was built without the Data Model. For a formula instead, use =COUNTA(UNIQUE(A2:A500)) in Excel 365 or 2021, or =SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&"")) in older versions.
Counting rows is easy. Counting how many different things are in those rows is where Excel gets awkward, because the obvious tool, a pivot table, hides the answer behind a checkbox you can only tick once, at creation time, and cannot tick at all on a Mac. This walks through every method that works, when each one is the right choice, and the specific reasons the pivot table option disappears.
What is a distinct count in Excel?
A distinct count tells you how many different values appear in a range, counting each one exactly once no matter how often it repeats. If a column of 400 invoice rows contains 37 different vendor names, the row count is 400 and the distinct count is 37. It answers questions like how many customers ordered this quarter, how many accounts appear on this statement, or how many suppliers we actually use.
Excel has no DISTINCTCOUNT worksheet function. That surprises people, and it is the reason this topic generates so many different answers. What Excel has instead is a pivot table aggregation borrowed from the Power Pivot engine, a modern dynamic array function, and a couple of formula tricks that predate both.
How do I get a distinct count in an Excel pivot table?
Distinct Count is available in pivot tables, but only when the pivot is built on the Data Model rather than on a plain range. The Data Model is the Power Pivot engine sitting underneath Excel, and Distinct Count is one of the aggregations it adds.
- Select your data, including the header row.
- Go to Insert > PivotTable.
- In the dialog, tick Add this data to the Data Model before you click OK. This is the step everybody misses.
- Build the pivot as normal, dragging the field you want counted into the Values area.
- Click that value field, choose Value Field Settings, and scroll to the very bottom of the Summarize Values By list. Distinct Count is the last entry.
It sits at the bottom because Excel groups it with the advanced aggregations rather than alongside Sum, Count and Average. If you have been scanning the top of that list, that is why you never found it.
Why is Distinct Count missing from my pivot table?
Almost always because the pivot table was created without the Data Model, and that decision cannot be changed afterwards. There is no setting to convert an existing pivot table into a Data Model pivot. You have to delete it and insert a new one with the box ticked. The other common cause is Excel for Mac, which does not support the Data Model at all.
A few other things to check. Refreshing an existing pivot will not add the option. Neither will adding the field a second time. And if you inherited the workbook from a colleague, the pivot they built may look identical to yours while behaving differently, purely because of a checkbox ticked months ago.
How do I do a distinct count in Excel on a Mac?
You cannot use the pivot table method, because Excel for Mac does not support Power Pivot or the Data Model. The Add this data to the Data Model checkbox either does not appear or appears greyed out, and no amount of updating changes that. Mac users have two good workarounds and both are genuinely fine.
The first is Power Query, which is available on modern Mac builds. Select your data, go to Data > From Table/Range, then in the query editor use Group By and set the operation to Count Distinct Rows. Load the result back to a sheet. This scales well and refreshes cleanly when the source data changes.
The second is a helper column, which works in every version of Excel on every platform. Add a column next to your data with this formula, filled down:
=1/COUNTIF($A$2:$A$400,A2)
Each row gets a fraction equal to one divided by the number of times its value appears. A vendor appearing four times contributes 0.25 four times, which sums to exactly 1. Put that helper column into the pivot table's Values area and set it to Sum rather than Count. The total is your distinct count, and it slices correctly by any row or column field you add.
How do I count distinct values with a formula?
In Excel 365 and Excel 2021, wrap UNIQUE in COUNTA:
=COUNTA(UNIQUE(A2:A500))
There is one trap. If the range contains empty cells, UNIQUE returns a zero for the blanks and COUNTA dutifully counts it, inflating your answer by one. Filter the blanks out first:
=COUNTA(UNIQUE(FILTER(A2:A500,A2:A500<>"")))
That version is the one to standardize on. It costs nothing on a clean range and saves you from an off by one error on a range with gaps, which is exactly what you get from data that arrived out of a converted document.
How do I count unique values in older Excel without UNIQUE?
Use SUMPRODUCT with COUNTIF. The classic form is =SUMPRODUCT(1/COUNTIF(A2:A500,A2:A500)), which applies the same fractions trick as the helper column, all inside one cell. It works in every version back to Excel 2007.
It also breaks the moment your range contains a blank cell, because COUNTIF returns zero for the blank and you get a divide by zero error. The hardened version handles that:
=SUMPRODUCT((A2:A500<>"")/COUNTIF(A2:A500,A2:A500&""))
Appending an empty string inside COUNTIF stops the zero, and the leading comparison contributes 0 for blank rows so they never count. Be aware this formula is slow over tens of thousands of rows, since it evaluates the whole range against itself. Past roughly 20,000 rows, switch to Power Query or a Data Model pivot.
What is the difference between distinct count and unique count?
They are used interchangeably in conversation but they mean different things, and Excel actually exposes both. A distinct count counts each different value once, so a vendor appearing five times contributes 1. A unique count, in the strict sense, counts only values that appear exactly once, so that same vendor contributes nothing.
The UNIQUE function covers both through its third argument. Microsoft documents exactly_once as a logical value that returns rows or columns occurring exactly once in the range. Omitted or FALSE, you get all distinct values. Set to TRUE, you get only the one time occurrences:
=COUNTA(UNIQUE(A2:A500,,TRUE))
That second form is genuinely useful for finding anomalies: a customer who ordered once and never came back, or an account code that shows up a single time in a ledger and probably should not.
Which method should I use?
| Method | Works in | Best for | Watch out for |
|---|---|---|---|
| Pivot table, Data Model | Excel for Windows | Slicing distinct counts by date, region or category | Must tick the box at creation; not available on Mac |
| COUNTA with UNIQUE | Excel 365, 2021, web | A single number on a dashboard | Blanks count as a value unless you FILTER them out |
| SUMPRODUCT with COUNTIF | Excel 2007 and later | Older workbooks and shared files | Divide by zero on blanks; slow past ~20,000 rows |
| Power Query, Group By | Windows and Mac | Large data and anything you refresh regularly | An extra step to load results back to a sheet |
| Helper column, then Sum | Every version | Mac users who still want a pivot table | Adds a column to the source data |
Counting distinct values in data that came out of a PDF
This is where distinct counts earn their keep and also where they quietly lie to you. When a statement, ledger or vendor report arrives as a PDF and gets converted to a spreadsheet, the first genuinely useful question is usually a distinct count: how many different accounts are on this statement, how many suppliers are in this ledger, how many invoice numbers came across.
Three things distort the answer on converted data. Trailing spaces are the worst, because ACME Supply and ACME Supply are two distinct values to Excel and identical to your eye. Run TRIM over the column first. Case is the opposite problem: COUNTIF and UNIQUE are both case insensitive, so Acme and ACME collapse into one, which is usually what you want but is worth knowing before you report the number.
The third is inconsistent spelling in the source document itself, where the same vendor is written three ways across a year of statements. No formula fixes that. Sort the distinct list and read it, which takes a minute and always finds something. Our guide to cleaning up data in Excel after a PDF conversion covers the trimming and normalizing steps in order, and removing duplicates in Excel is the natural next step once you know how many distinct values you are dealing with.
One practical note on scale. A distinct count over a few thousand rows is a spreadsheet job. Once you are counting distinct customers across several years of transactions, Excel starts to strain and the more comfortable move is to load the data somewhere you can ask a database the same question in plain English instead of maintaining formulas over half a million rows.
Troubleshooting a distinct count that looks wrong
| Symptom | Likely cause | Fix |
|---|---|---|
| Distinct Count is not in the pivot menu | Pivot was built without the Data Model | Delete the pivot and reinsert with the box ticked |
| Count is one higher than expected | Blank cells counted as a value by UNIQUE | Wrap the range in FILTER to exclude blanks |
| #DIV/0! from SUMPRODUCT | Blank cells in the range | Use the hardened form with &"" inside COUNTIF |
| Same vendor counted twice | Trailing space or a non breaking space | Apply TRIM, then CLEAN, then recount |
| Numbers counted separately from identical numbers | Some values are text after a conversion | See converting text to numbers |
| Workbook crawls on every edit | SUMPRODUCT over a very large range | Move to Power Query or a Data Model pivot |
The short version
On Windows, build the pivot with the Data Model and use Distinct Count, because it slices by every other field and stays correct when you filter. On a Mac, use Power Query's Count Distinct Rows, or the helper column trick if you want to stay in a pivot table. For a single number anywhere, =COUNTA(UNIQUE(FILTER(range,range<>""))) is the cleanest formula there is. And whichever you pick, trim the column before you trust the result, especially if the data started life as a PDF.
If your source is still a document rather than a spreadsheet, start by converting the PDF to Excel, then check the conversion for errors before you count anything. A distinct count is only as honest as the column it reads.