How to Count Duplicates in Excel

To count duplicates in Excel, COUNTIF does nearly all the work: point it at the list and at one value, and it tells you how many times that value appears. The tricky part is deciding what "how many duplicates" means, because there are three common answers. This guide shows each one on the same short list, plus duplicates across two columns and the data problems that hide repeats.

Three ways to count duplicates#

Every example uses this customer list in A1:A8:

Ana appears 3 times, Ben twice, and Cara and Dan once each. Now, how many duplicates are there? It depends on the question:

All three are correct. Pick the one your task actually asks for, then use the matching formula below. In a job test, read the question twice: "how many customers ordered more than once" is the repeated-values count (2), while "how many orders came from repeat customers" is the duplicate-rows count (5).

Step 1: Count how often each value appears#

A helper column with COUNTIF shows the count for every row. In B2, type:

=COUNTIF($A$2:$A$8,A2)

Fill it down to B8 (Windows: Ctrl+D; Mac: ⌘D). The results are 3, 2, 1, 3, 1, 2, 3. Each number is how many times that row's name appears in the whole list.

The $ signs lock the list so it stays A2:A8 on every row, while A2 changes to A3, A4 and so on. Without them, the range would shrink as you fill and the counts would be wrong. For more on how COUNTIF works, see how to use COUNTIF.

To turn the counts into labels, wrap them in IF:

=IF(COUNTIF($A$2:$A$8,A2)>1,"Duplicate","Unique")

Ana and Ben rows show Duplicate; Cara and Dan show Unique.

Step 2: Count all duplicate rows in one cell#

To get the total without a helper column, use SUMPRODUCT around COUNTIF:

=SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))

Here is how it works. COUNTIF(A2:A8,A2:A8) uses the whole list as the criteria, so it returns one count per row: {3,2,1,3,1,2,3}. >1 turns each count into TRUE or FALSE. -- turns those into 1s and 0s, and SUMPRODUCT adds them. The result is 5.

If you have a helper column of counts already, =COUNTIF(B2:B8,">1") gives the same 5.

Step 3: Count extra copies (first occurrence not counted)#

The number of extra copies is the number of rows minus the number of unique values. These are the rows that Data > Remove Duplicates would delete:

=ROWS(A2:A8)-SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) counts the unique values. Each of Ana's three rows adds 1/3, each of Ben's rows adds 1/2, and Cara and Dan add 1 each, so the total is 4. Seven rows minus 4 unique values gives 3. In Excel 2021 or Microsoft 365, =ROWS(A2:A8)-COUNTA(UNIQUE(A2:A8)) is easier to read and gives the same answer. Note that the 1/COUNTIF version fails with #DIV/0! if the range contains blank cells.

To flag only the second and later copies, use a range that grows as you fill down:

=COUNTIF($A$2:A2,A2)>1

Only the start of the range is locked, so on row 5 it checks A2:A5. The result is TRUE for row 5 (the second Ana), row 7 (the second Ben) and row 8 (the third Ana): three extra copies. The guide to counting unique values covers the unique side in more depth.

How to count values that are repeated#

To count how many different values appear more than once, divide each row's flag by its count, so each repeated value adds up to exactly 1:

=SUMPRODUCT((COUNTIF(A2:A8,A2:A8)>1)/COUNTIF(A2:A8,A2:A8))

Ana's three rows each add 1/3 and Ben's two rows each add 1/2. Cara and Dan add 0, because their flag is FALSE. The result is 2: Ana and Ben.

How to count duplicates across two columns#

Sometimes a row is only a duplicate when two values match together, such as the same customer on the same date. Say column B holds the order date. Use COUNTIFS with a pair for each column:

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2)

A result above 1 means another row has the same name and the same date. A customer who ordered on two different dates is not a duplicate by this rule. Add more pairs for more columns.

Why COUNTIF misses some duplicates#

COUNTIF compares values exactly, apart from capitals. These differences hide duplicates:

1. Extra spaces. "Ana " with a trailing space is a different value from "Ana". Clean the column with TRIM in a helper column first.
2. Spelling and punctuation. "Ana Lopez" and "Ana López" are different to Excel.
3. Long numbers. COUNTIF treats text that looks like a number as a number, and Excel keeps only 15 significant digits. Long IDs such as card or account numbers can then look like duplicates when they aren't.

COUNTIF is not case-sensitive, so "ana" and "ANA" count as the same name, which is usually what you want.

To see the duplicates rather than count them, use conditional formatting; see how to highlight duplicates. The same counting pattern also works for answers in a survey, as shown in how to count Yes, No and ticks.

Practise it#

COUNTIF with a locked range is the building block for every duplicate check. Practise COUNTIF in a live spreadsheet: you type the formula and the lesson checks it instantly. The COUNTIF function guide has the syntax on one page, and the exercise here lets you try the one-cell duplicate count.