How to count duplicates in Excel

Decide which "count of duplicates" you need

"How many duplicates are there?" has at least three right answers, and people often compare two of them and think one formula is broken. In the order export above, j.okafor appears 3 times, sam.li twice and m.lopez twice.

Entries that are duplicated counts every copy, including the first: 3 + 2 + 2 = 7. Extra copies counts only the repeats, which is what you would delete to clean the list: 2 + 1 + 1 = 4. Duplicated values counts the values that repeat at all: 3. Pick the one that answers your real question: "how many rows are affected" is the first, "how many will Remove Duplicates delete" is the second, and "how many customers ordered more than once" is the third.

How many times each value repeats

Put =COUNTIF($B$2:$B$11,B2) in a helper column and fill it down. The dollar signs keep the range fixed while B2 moves with each row. Any number above 1 is a duplicate, and the number tells you how many copies exist. Sort or filter the column, largest first, to see the worst offenders.

For a summary instead of a column, list each value once with its count. In Excel 365, HSTACK puts the unique emails next to their counts in one spill, and the count updates when new orders are added.

Total entries that are duplicates

COUNTIF with the whole range as the criteria returns a count for every row at once. Comparing that with >1 gives TRUE for each duplicated entry, the double minus turns TRUE and FALSE into 1 and 0, and SUMPRODUCT adds them up. It works in every version of Excel without Ctrl+Shift+Enter.

This is the number of rows that a Duplicate Values conditional format would colour, so use it when you want to check that highlight or report how much of a list is affected.

Extra copies, and how many values are duplicated

The number of extra copies is the number of entries minus the number of distinct values. ROWS counts the entries and ROWS(UNIQUE()) counts the distinct ones. This is the figure Data > Remove Duplicates reports when it says "4 duplicate values found and removed", so use it to know in advance what a clean-up will delete. In Excel 2019 or older, use =ROWS(B2:B11)-SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11)), which needs a range with no blank cells.

To count how many values repeat at all, divide each duplicate flag by its own count, so each repeated value adds up to exactly 1: =SUMPRODUCT((COUNTIF(B2:B11,B2:B11)>1)/COUNTIF(B2:B11,B2:B11)). For this list it returns 3, meaning three customers ordered more than once.

Duplicate rows across several columns

A duplicate row means every column matches, not just the email. Order 50211 was imported twice with the same email, product and quantity, and so was 50215. j.okafor's order 50224 is a different order, so it is not a duplicate row.

In Excel 365, UNIQUE on a multi-column range compares whole rows, so the same subtraction counts duplicate rows. In older versions, add a helper column with =COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2,$C$2:$C$11,C2,$D$2:$D$11,D2) and count the values above 1; that gives the rows involved (4 here) rather than the extra copies (2).

Why your count is off: case, spaces and numbers as text

COUNTIF, UNIQUE and Remove Duplicates all ignore upper and lower case, so M.Lopez and m.lopez count as the same email. That is usually what you want for emails. For a case-sensitive count of one value, use =SUMPRODUCT(--EXACT(B2:B11,B7)), which counts only exact matches: 1 here.

Spaces are not ignored. "[email protected] " with a trailing space is a different value to every one of these tools, so a real duplicate goes uncounted. Clean the text before counting: UNIQUE accepts a calculated range, so =ROWS(B2:B11)-ROWS(UNIQUE(TRIM(B2:B11))) counts 5 extra copies. For a lasting fix, trim the column itself.

Two more traps: COUNTIF treats * and ? in a value as wildcards, so a code like AB*1 also counts AB-X1. And COUNTIF counts the number 1001 and the text "1001" as a match, while UNIQUE keeps them apart, so the two methods can disagree on imported IDs.