# How to count duplicate values in Excel

Count the rows whose value appears more than once: `=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))`.

**Excel:** `=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Invoice |  | Rows that are duplicates |
| INV-1001 |  |  |
| INV-1002 |  |  |
| INV-1003 |  |  |
| INV-1002 |  |  |
| INV-1004 |  |  |
| INV-1005 |  |  |
| INV-1003 |  |  |
| INV-1006 |  |  |
| INV-1002 |  |  |
| INV-1007 |  |  |
| INV-1008 |  |  |

Formula in D1: `=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)>1))`

Result: 5

## 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.

## Other ways

- `=ROWS(A2:A12)-COUNTA(UNIQUE(A2:A12))`: 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)))`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-duplicate-values