Count Unique Values in Excel: UNIQUE, COUNTIF and Pivots

Thornbury Waste & Recycling runs commercial bin collections across North Somerset from a depot in Clevedon and a satellite yard in Nailsea. The council contract is a simple one, and its simplicity is the whole story: £31.40 per collection point serviced per month. Per point, not per lift. A restaurant emptied three times a week is one collection point, the same as a village hall emptied once a month.

The monthly return is built from the round sheets, which arrive as one row per lift. March 2026 came to 4,319 rows in a sheet called Round, with the site code in column C.

The analyst did what anybody would do. Copied column C to a spare sheet. Data ▸ Data Tools ▸ Remove Duplicates. One column ticked. OK.

Duplicate values found and removed: 3,527. 792 unique values remain.

792 × £31.40 = £24,868.80, invoiced on 3 April and paid on 2 May.

The round services 713 collection points. It has serviced 713 for two years; the number is on a whiteboard in the Clevedon transport office.

Where the Other Seventy-Nine Came From

Not fraud, not fiction, not a formula error. Seventy-nine rows where one real collection point was written two different ways:

• 61 carried a trailing space. The Nailsea yard still keys its round sheet in a legacy Access front end that pads every code to twelve characters. BS22-0149 and BS22-0149 are two different pieces of text, and every tool in Excel will tell you so.
• 14 carried a non-breaking space, CHAR(160), picked up from a web portal the commercial team copies new sites out of. It looks exactly like a space, it is not a space, and TRIM does not remove it.
• 3 were stored as numbers rather than text. Three sites on an old numbering scheme have all-digit codes, and one of the two source systems exports them as numbers. 8821 and "8821" are the same code and different values.
• 1 was an empty cell. A lift on 14 March was cancelled at the kerbside and the driver's tablet wrote the row without a site code.

That last one is worth saying slowly. An empty cell was invoiced as a collection point at £31.40 a month, for fourteen months, and nobody noticed because an empty cell does not look like anything.

What It Cost

North Somerset's contract performance team re-ran the March return against the depot's site register in June 2026 as part of a routine three-year review. They recomputed every month back to February 2025 — fourteen returns, an average of 79 phantom points — and recovered £34,728.40.

That is the cheap part. The expensive parts were a letter from the council's Section 151 officer, a contract-compliance note that sits on the file until the 2028 re-tender, and eleven days of a finance analyst's time reconstructing fourteen monthly returns from round sheets, because nothing in the workbook recorded how any of the original figures had been produced. Remove Duplicates leaves no trace: no formula, no settings, no date, nothing in any cell.

And here is the part that makes this article worth writing. Nothing was wrong with Remove Duplicates. It did exactly what it says. So did UNIQUE, so did COUNTIF, so did the Data Model's Distinct Count — all four of them return 792 on that column, and all four are correct. The question "how many different values are in this column" has one answer and it is 792. The question the contract asks is "how many collection points were serviced", and no tool in Excel can answer that until somebody writes down, in a cell, what makes two codes the same code.

What this covers. COUNTA, COUNT, COUNTIF, COUNTIFS, SUMPRODUCT, TRIM, SUBSTITUTE, UPPER, EXACT, LEN and SUM work in every version of Excel, on every platform. UNIQUE, FILTER, SORT and LET need Microsoft 365 or Excel 2021 — the pre-365 route is section 4 and it is still the right choice in a workbook other people open in older versions. A pivot table's Distinct Count needs the Data Model: Windows Excel 2013 and later, and not available in Excel for Mac or Excel for the web, where section 3 or section 4 is the answer instead. The example is waste collection because the contract pays per site, but the shape is every per-seat licence reconciliation, every unique-visitor report and every "how many customers did we actually serve" question anybody has ever been asked at short notice.

1) The Column, Counted Eight Ways

Three things in that table are worth saying out loud.

The methods agree with each other. Remove Duplicates and =ROWS(UNIQUE(C2:C4320)) both say 792, to the row. That is not a coincidence and it is not luck — they implement the same comparison. When two different tools give you the same wrong number, the instinct is to believe it. The thing they agree about is the data, not the contract.

Each line of the ladder removes one definition of "the same". 792 counts distinct text. 728 counts distinct text once ordinary whitespace and type are set aside. 714 adds the non-breaking space. 713 adds "and a blank is not a site". Every step down that ladder is a decision somebody made, and the figure is only defensible because the decisions are in cells where an auditor can read them.

The last row is not on the ladder at all. 96 is the count of sites serviced exactly once in March — a perfectly reasonable thing to want, computed with the same function, described with the same English word, and 617 away from the answer.

🎯 Scenario: Before you count anything, put two cells side by side: =ROWS(C2:C4320) and =COUNTA(C2:C4320). On this sheet they say 4,319 and 4,318. That one-row gap is every blank cell in the column, and it is the cheapest warning in this article.

2) "Unique" Means Two Different Things

This is the first fork and most of the wrong numbers in the world are downstream of it.

Given the column A, B, B, C, C, C:

Both are "unique values" in ordinary speech. Only one of them is what a per-site contract, a per-seat licence count or a headcount of distinct customers means.

The second one has its own uses and they are real: parts ordered only once all year, patients seen a single time, the test case that ran once. It is also the formula people reach for when they half-remember COUNTIF, and it will quietly hand you a number that looks plausible — 96 is a believable count of sites, right up to the moment somebody multiplies it by £31.40.

Say which one you mean in the cell next to it. A label is not decoration. Distinct sites serviced and Sites serviced once only are nine words that would have ended this entire story in April 2025.

🎯 Scenario: Put both formulas on the sheet, always, with labels. If they are close together, your data has few repeats. If one is 713 and the other is 96, you have just learned something about the round as well as about the formula.

3) UNIQUE, and the Blank That Comes Back as a Value

UNIQUE returns the list; you count the list.

=ROWS(UNIQUE(C2:C4320))        the number of distinct values
=UNIQUE(C2:C4320)              the values themselves, spilled
=SORT(UNIQUE(C2:C4320))        the same, in order, for eyeballing

Use ROWS, not COUNTA. COUNTA(UNIQUE(...)) happens to give the same answer here, and it gives a different one the moment the spilled list contains an empty string, because COUNTA counts "" and ROWS counts rows. One function that always counts the list is better than two that sometimes do.

The trap is the blank. UNIQUE over a range containing empty cells returns 0 for them — a zero, in the list, as a value. So =ROWS(UNIQUE(C2:C4320)) is 792 where the distinct site codes number 791, and the extra is an empty cell promoted to a site.

The fence is FILTER:

=ROWS(UNIQUE(FILTER(C2:C4320,C2:C4320<>"")))

Two things to know about that expression. FILTER returns #CALC! when nothing passes the test — a column that is entirely empty gives you an error rather than a zero, which is correct and unhelpful on a dashboard, so wrap it where a zero is the sane answer:

=IFERROR(ROWS(UNIQUE(FILTER(K2:K4320,K2:K4320<>""))),0)

And UNIQUE works along rows too, if your data is the wrong way round: =COLUMNS(UNIQUE(C2:Z2,TRUE)). The second argument switches it to comparing columns, and then you count columns.

If you are counting distinct values across a whole column of a Table, let the Table carry the range — =ROWS(UNIQUE(FILTER(Round[Site],Round[Site]<>""))) grows on its own, and a range that grows on its own is one fewer reason for next month's figure to be wrong.

🎯 Scenario: On your own data, run =ROWS(UNIQUE(C2:C4320)) and =ROWS(UNIQUE(FILTER(C2:C4320,C2:C4320<>""))) side by side. If they differ, the difference is 1, and that 1 is a blank being counted as a thing.

4) The Formula for Everyone Without UNIQUE

UNIQUE arrived with dynamic arrays. In Excel 2019, 2016 and every workbook somebody opens in an older version at a client site, this is the formula, and it has been the formula since the 1990s:

=SUMPRODUCT(1/COUNTIF(C2:C4320,C2:C4320))

Why it works, because it is worth understanding rather than copying. COUNTIF(range,range) returns an array the same height as the range, holding for each cell the number of times that cell's value occurs. A value appearing three times contributes 1/3 three times, which is 1. A value appearing once contributes 1/1 once, which is 1. Every distinct value contributes exactly 1 however often it appears, and the sum is the distinct count.

Why it broke here. An empty cell has a COUNTIF of 0, 1/0 is #DIV/0!, and one #DIV/0! in an array poisons the whole sum. That is the error in row 8 of the table, and it is why the analyst stopped using the formula and went to Remove Duplicates in the first place: the formula told the truth in a way that looked like a fault.

The fix is to fence the blanks on the top and bottom of the fraction at once:

=SUMPRODUCT((C2:C4320<>"")/COUNTIF(C2:C4320,C2:C4320&""))

The numerator is 0 for a blank row, so blank rows contribute nothing. The &"" in the criteria turns the blank's criteria into an empty string, which COUNTIF can actually count, so the divisor is never zero and never produces the error the numerator was going to discard anyway.

What it costs. This formula compares every cell with every other cell: 4,319 rows is 18.6 million comparisons, which Excel does without blinking. 200,000 rows is 40 billion, which it does not. Past about 20,000 rows, move the calculation to a key column plus UNIQUE, or to a Data Model pivot — section 7 — rather than waiting for a recalculation you can time with a kettle.

🎯 Scenario: Take any column you have already counted with UNIQUE and run the SUMPRODUCT form on the same range. When the two disagree, you have found a type or case difference, and section 5 says which.

5) What COUNTIF Thinks Is the Same Value

The two formula families do not define "same" identically, and on this column they differ by exactly 3. The difference is not a bug in either; it is a property of COUNTIF that is useful about half the time and dangerous the other half.

COUNTIF folds numbers and text that look like numbers. COUNTIF(range,"8821") matches the number 8821 as well as the text "8821". So the SUMPRODUCT form treats the three number-stored codes as the same value as their text twins, and returns 788 against UNIQUE's 791 on the raw column. For a site code, COUNTIF is accidentally right and UNIQUE is literally right. For a column where "0049" and 49 are genuinely different things — a product code, a sort code, a cost centre — COUNTIF is quietly wrong and will not say so.

Both fold case. "bs22-0149" and "BS22-0149" are one value to COUNTIF, to UNIQUE, to Remove Duplicates and to a Data Model Distinct Count. Excel folds case nearly everywhere, which is why none of the 79 phantoms here was a capitalisation difference — the two source systems disagree about case constantly and it has never cost anybody a penny.

If you genuinely need a case-sensitive distinct count — distinguishing "ab" from "AB", which matters for hashes, barcodes with check characters and some bank references — EXACT is the only comparison in Excel that sees case. Count first occurrences with an expanding range:

L2:  =IF(SUMPRODUCT(--EXACT($C$2:C2,C2))=1,1,0)      fill down
     =SUM(L2:L4320)                                   case-sensitive distinct count

COUNTIF reads wildcards in its criteria. A value containing * or ? is a pattern, not a string, so CAT* matches CATALOGUE and the count comes back high. Escape with a tilde — SUBSTITUTE(SUBSTITUTE(C2,"~","~~"),"*","~*") — or do not use COUNTIF on columns that contain them. UNIQUE has no wildcards and no such problem.

COUNTIF criteria stop at 255 characters. Longer text returns #VALUE!. Free-text fields, email subjects and pasted URLs hit this, and the error is the good outcome — the bad one is a truncated comparison you do not notice. Where values are long, count distinct with UNIQUE, or key on a shorter extract.

Neither of them ignores spaces. That is section 9, and it is the one that cost £34,728.40.

🎯 Scenario: In a spare cell, =COUNT(C2:C4320) on a column of identifiers. COUNT counts numbers only, so on a column of codes it must be 0. Here it says 3, and those 3 are the entire disagreement between your two formulas.

6) Remove Duplicates Is an Edit, Not a Count

Remove Duplicates deletes rows. It is a data operation that happens to display a count on its way past, and the count is the only part anybody wanted.

Three consequences, all of which happened here.

It destroys the thing it counted. The copy of column C on the spare sheet went from 4,319 rows to 792 and the original rows are gone. On a copy that is harmless. Run it on the round sheet itself — which somebody will, in a hurry, one day — and the lifts, dates, weights and vehicle numbers on those 3,527 rows are gone with it, with only Ctrl+Z between you and a reconstruction.

It compares only the columns you tick, and it keeps the first row it meets. Tick Site alone and the surviving row keeps whatever date and weight happened to be on the first lift of that site. Nothing about the surviving row is wrong, and nothing about it is representative either.

It leaves no record. The number 792 existed in a dialog box for four seconds. Nothing in the workbook says which column was de-duplicated, whether the header checkbox was ticked, or when. Fourteen monthly figures were produced this way and the reconstruction in June had to start from the round sheets, because the returns themselves contained no working at all.

If you want the list rather than the count, and you want it without destroying anything, that is Data ▸ Advanced Filter ▸ Unique records only, with Copy to another location set. It writes the distinct values somewhere else and leaves the source intact — the same answer as UNIQUE, available in every version, and still a one-off operation that records nothing about itself.

🎯 Scenario: Anywhere a de-duplicated count is reported monthly, replace the tool with a formula in a cell, even if the formula is longer. A number you can re-derive in June is a number; a number somebody read off a dialog in April is an anecdote.

7) The Pivot Table's Count Is Not a Distinct Count

Drop the Site field into the Values area and Excel offers Count of Site, which counts rows: 4,318 — the same figure as COUNTA, because that is the same question. Nothing in the standard pivot cache counts distinct values, and a great many "unique customers" figures in a great many monthly packs are this number.

Distinct Count exists, and it needs the Data Model. Build the pivot with Insert ▸ PivotTable and tick Add this data to the Data Model, then in Value Field Settings scroll to the bottom of the summary list, past Count and Average and StdDev, to Distinct Count. On column C it reports 792, which is the correct distinct count of what that column holds, and on a cleaned key column it reports 713.

This is the fastest route by a distance when the data is large — it counts distinct in the engine rather than comparing 18 million pairs of cells on the grid — and it breaks down a distinct count by depot, by month, by vehicle without another formula. The costs are real and worth knowing before you build a pack on it: Distinct Count is Windows Excel 2013 and later, it is not in Excel for Mac or Excel for the web, a Data Model pivot cannot be GETPIVOTDATA'd quite as freely as a normal one, and the model is saved inside the workbook, which grows it.

Subtotals do not add up, and that is correct. A distinct count of sites by depot gives Clevedon 509 and Nailsea 213, which sum to 722 against a grand total of 713. Nine sites are serviced by both depots, and a distinct count counts each of them once in each depot's row and once overall. Every distinct-count report in existence has this property, every reader eventually spots it, and the only defence is a line of text under the table saying so.

🎯 Scenario: If a monthly pack contains the words "unique" or "distinct" anywhere, open the pivot and check the Value Field Settings. If it says Count, the number has been wrong for as long as the pack has existed.

8) Unique Counts Per Group

The question is almost never "how many distinct sites" on its own. It is distinct sites per depot, per month, per vehicle, per contract.

With dynamic arrays, nest the group test inside FILTER:

=ROWS(UNIQUE(FILTER($K$2:$K$4320,($D$2:$D$4320=H2)*($K$2:$K$4320<>""))))

The * is AND — multiply the conditions, do not try to use AND, which collapses an array to a single TRUE. H2 holds the depot name, so the formula fills down a small table of depots. For the full breakdown in one cell, =UNIQUE(D2:D4320) spills the depot list beside it.

Without dynamic arrays, the COUNTIF idea extends to COUNTIFS:

=SUMPRODUCT(($D$2:$D$4320=H2)/COUNTIFS($D$2:$D$4320,$D$2:$D$4320&"",$K$2:$K$4320,$K$2:$K$4320&""))

The divisor now counts each depot-and-site combination, so a site serviced four times from Clevedon contributes 1/4 four times and the depot gets one site. Rows from other depots have a numerator of 0 and drop out. The &"" on both criteria does the same blank-fencing job as before.

A warning about reading the results. As in section 7, per-group distinct counts do not sum to the overall distinct count unless every value belongs to exactly one group. Here they sum to 722 against 713. If a reader has to reconcile those two numbers under pressure, they will assume one is wrong.

🎯 Scenario: Build the depot table with UNIQUE spilling the groups and the formula above beside it, then put =SUM(...) under it and =ROWS(UNIQUE(FILTER(K,K<>""))) beside that. Label the gap "sites on two rounds" rather than waiting to be asked about it.

9) The Key Column, Which Is the Actual Answer

Every section above counts whatever is in the cells. This is the section where somebody decides what a site code is.

One expression, in a helper column, is enough for all four of this column's problems:

K2:  =UPPER(TRIM(SUBSTITUTE(C2,CHAR(160)," ")))

Reading it inside out:

• SUBSTITUTE(C2,CHAR(160)," ") turns non-breaking spaces into ordinary ones. CHAR(160) is what a copy from a web page brings with it; it renders identically to a space in every font Excel ships, and TRIM does not touch it. This was 14 of the 79. CLEAN does not touch it either — CLEAN removes characters 0 to 31, and 160 is not among them.
• TRIM removes leading and trailing spaces and collapses internal runs to one. This was 61 of the 79. It also, incidentally, returns text — so the three codes stored as numbers come out as text and stop being a separate value. That is a side effect worth knowing in both directions: TRIM on a column of real numbers turns them into text and your SUM goes to zero.
• UPPER is belt and braces. Every method in this article already folds case, so it changes nothing — but it makes the intention explicit, and it protects the column if somebody later counts it with a case-sensitive EXACT comparison or exports it to a system that cares.

Then count the key column, not column C, with whichever method suits the version you are in:

=ROWS(UNIQUE(FILTER(K2:K4320,K2:K4320<>"")))            365 / 2021        → 713
=SUMPRODUCT((K2:K4320<>"")/COUNTIF(K2:K4320,K2:K4320&""))  any version    → 713
Data Model pivot, Distinct Count of Key                   Windows 2013+   → 713

All three agree, because they were finally asked the same question.

Two notes on using it. A LET keeps it to one cell if you would rather not add a column: =LET(k,UPPER(TRIM(SUBSTITUTE(C2:C4320,CHAR(160)," "))),ROWS(UNIQUE(FILTER(k,k<>"")))). And do not normalise in silence — a key column that quietly folds two codes together is the same failure in the other direction. Put a count of what it changed next to it: =SUMPRODUCT(--(C2:C4320<>K2:K4320)) says 78 on this sheet, and 78 is a number somebody should have to approve.

🎯 Scenario: Add the key column to the sheet you report from this month, and put =SUMPRODUCT(--(C2:C4320<>K2:K4320)) in the cell above it. Anything other than 0 is the size of the problem you have been counting for however long the report has existed.

10) Distinct Pairs Across Two Columns

"How many site-days did we service" is a distinct count over two columns at once, and the obvious route is a concatenated key:

=ROWS(UNIQUE(K2:K4320&"|"&TEXT(E2:E4320,"yyyy-mm-dd")))

That works, and it has one failure mode you must rule out: if the delimiter can occur inside either value, two different pairs can produce one key. A|B with C and A with B|C are both A|B|C. Pick a character the data cannot contain, and check it: =SUMPRODUCT(--ISNUMBER(FIND("|",K2:K4320))) must be 0.

With dynamic arrays you do not need the key at all. UNIQUE over a two-column range returns distinct rows:

=ROWS(UNIQUE(D2:E4320))             distinct depot-and-date pairs, directly

No delimiter, no TEXT, no collision — it compares the row as a row. This is also the honest way to count distinct customers where identity is a combination: name plus postcode, project plus client, part plus revision.

And note what TEXT is doing in the first formula: a date column that carries a time of day will make every lift its own distinct day. TEXT(...,"yyyy-mm-dd") or INT flattens it. A timestamp hiding in a date column is one of the commonest reasons a distinct count comes back suspiciously close to the row count.

🎯 Scenario: Count distinct site-days both ways on your own data — concatenated key and two-column UNIQUE. If they differ, your delimiter appears in the data, and the two-column form is the one that is right.

11) Seven One-Cell Checks

Put these on the sheet, not in your head. Each is one cell, and each catches one of the seventy-nine.

1. Are there blanks? =ROWS(C2:C4320)-COUNTA(C2:C4320) → 1. Anything non-zero means a blank is about to become a value.
2. Are there stray spaces? =SUMPRODUCT(--(C2:C4320<>TRIM(C2:C4320))) → 61. Rows whose text changes when trimmed.
3. Are there non-breaking spaces? =SUMPRODUCT(--ISNUMBER(FIND(CHAR(160),C2:C4320&""))) → 14. The ones TRIM cannot see.
4. Are there numbers among the codes? =COUNT(C2:C4320) → 3. On an identifier column this must be 0.
5. Do the two formula families agree? =ROWS(UNIQUE(FILTER(K2:K4320,K2:K4320<>""))) against =SUMPRODUCT((K2:K4320<>"")/COUNTIF(K2:K4320,K2:K4320&"")). Equal means no type or wildcard surprises left.
6. How much did normalising change? =SUMPRODUCT(--(C2:C4320<>K2:K4320)) → 78. The number somebody signs off.
7. Does the invoice reconcile? =ROWS(UNIQUE(FILTER(K2:K4320,K2:K4320<>"")))*31.4 → £22,388.20, against the figure on the invoice. Had this one cell existed in April 2025, the other six would never have been needed.

🎯 Scenario: Three of these — 1, 2 and 7 — take about ninety seconds to add to a report you already produce. Run them on last month's figures before you run them on this month's.

12) Twelve Traps

1. "Unique" means distinct values to one person and appears-exactly-once to another. 713 and 96 from the same column. Label the cell.
2. A trailing space is a different value. To Remove Duplicates, UNIQUE, COUNTIF and Distinct Count alike. TRIM first, count second.
3. TRIM does not remove CHAR(160). The non-breaking space that comes out of every web page and PDF survives it. SUBSTITUTE it out explicitly.
4. UNIQUE returns a blank cell as 0, so an empty row becomes a distinct value. Fence it with FILTER.
5. SUMPRODUCT(1/COUNTIF(...)) is #DIV/0! if one cell is empty. Use the (range<>"")/COUNTIF(range,range&"") form, which handles it on both sides of the fraction.
6. COUNTIF folds 8821 with "8821" and UNIQUE does not. The two give different distinct counts on mixed-type columns, and which one is right depends on your data, not on Excel.
7. COUNTIF criteria read wildcards. A value containing * or ? matches more than itself. Escape with ~, or count with UNIQUE.
8. COUNTIF criteria stop at 255 characters. Long text gives #VALUE! at best.
9. Everything folds case except EXACT. If "ab" and "AB" must count as two, build the count on EXACT; otherwise stop worrying about case entirely.
10. A pivot's Count of a field is a row count. Distinct Count needs the Data Model, and is Windows-only.
11. Per-group distinct counts do not sum to the total. 509 + 213 = 722 against 713, because nine sites are on two rounds. Say so under the table.
12. Remove Duplicates deletes rows and records nothing. It is an edit with a count in the corner, and the count is gone four seconds later.

Thornbury still counts its collection points in Excel, and still counts them monthly, and the return still goes to the council as a spreadsheet.

What changed on Round is three cells and a column. Column K holds =UPPER(TRIM(SUBSTITUTE(C2,CHAR(160)," "))). Above the header sit the distinct count, the count of rows the key column altered, and the invoice reconciliation — 713, 78, £22,388.20 — each with a label a council officer can read without being talked through it. The transport office's whiteboard figure is in a fourth cell, typed by hand, and a fifth says whether the two match.

The general lesson is not about a function. It is that a distinct count is a definition before it is a calculation, and Excel will never ask you for the definition. It will take a column, compare the values exactly as they are stored, and give you a confident number — 792, correct, defensible, and 79 collection points away from what the contract says. Remove Duplicates was not wrong on 3 April 2025. It was never asked the question, because nobody had written it down.