How to count duplicate values in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))Count the rows whose value appears more than once: =SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1)).
- Result on the example
- 5 in
D1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D1. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF(A2:A12,A2:A12) returns, for every row, how many times its invoice number appears in the list. Rows with a count above 1 are duplicates, and SUMPRODUCT counts them.
INV-1002 appears three times and INV-1003 twice, so five rows are involved in duplicates.
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))Result5
A2:A1211 rows × 1 col: "INV-1001", "INV-1002", "INV-1003", "INV-1002", …A range: 11 cells in one column.
COUNTIF(A2:A12,A2:A12){11 rows × 1 col} 1, 3, 2, 3, …COUNTIF: Counts cells that meet one condition.- range
A2:A1211 rows × 1 col: "INV-1001", "INV-1002", "INV-1003", "INV-1002", … - criteria
A2:A1211 rows × 1 col: "INV-1001", "INV-1002", "INV-1003", "INV-1002", …
- range
COUNTIF(A2:A12,A2:A12)>1{11 rows × 1 col} FALSE, TRUE, TRUE, TRUE, …The > operator tests greater than.
-(COUNTIF(A2:A12,A2:A12)>1{11 rows × 1 col} 0, -1, -1, -1, …Makes the value negative.
--(COUNTIF(A2:A12,A2:A12)>1{11 rows × 1 col} 0, 1, 1, 1, …Makes the value negative.
SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(COUNTIF(A2:A12,A2:A12)>1{11 rows × 1 col} 0, 1, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1)). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =ROWS(A2:A12)-COUNTA(UNIQUE(A2:A12)) | 3Different result | Counts the extra copies only: the rows Remove Duplicates would delete. Returns 3: two extra INV-1002 rows and one extra INV-1003. |
| =COUNTA(UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))) | 2Different result | Counts how many different invoice numbers are duplicated. Returns 2: INV-1002 and INV-1003. |
Common mistakes
- "Count duplicates" can mean three numbers: rows involved (5), extra copies (3), or values that repeat (2). Decide which one your report needs.
- COUNTIF ignores case and treats "inv-1002" as a duplicate of "INV-1002". Use EXACT if case matters.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count duplicate values
D1, count the rows in A2:A12 whose invoice number appears more than once.Questions
How do I mark each duplicate row?
Next to the data, use =COUNTIF($A$2:$A$12,A2)>1 and fill down; it returns TRUE on every duplicate.
How do I count duplicates across two columns?
Use COUNTIFS with both columns: =SUMPRODUCT(--(COUNTIFS(A2:A12,A2:A12,B2:B12,B2:B12)>1)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.