Cleanup formula page

Count Duplicates Formula

Use this when you need a single number showing duplicate volume.

Best for

Count duplicate values in Excel or Google Sheets.

What it returns

The result is the number of nonblank entries beyond the unique count.

Copy formulas

Excel formula

=COUNTA(A2:A100)-SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100))

Google Sheets formula

=COUNTA(A2:A100)-COUNTUNIQUE(FILTER(A2:A100, A2:A100<>""))

Excel / Google Sheets difference

The Excel formula subtracts distinct nonblank values from the nonblank total, while the Google Sheets version uses COUNTUNIQUE after filtering blanks; both count extra occurrences.

Example data

Email	Region	Status
maya@example.com	East	Active
noah@example.com	West	Active
maya@example.com	East	Active
	West	Inactive

What it returns

The result is the number of nonblank entries beyond the unique count.

How the formula works

The formula compares filled row count against unique value count.
Blank rows are excluded.
Use a flag formula if you need row-by-row labels instead.

Syntax piece	Role in the formula
COUNTA(A2:A100)	The total number of nonblank source cells.
unique count	The number of distinct nonblank values.
subtraction	The difference that represents occurrences beyond the first copy.

Verified examples

Sample extra occurrences

=COUNTA(A2:A100)-SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100))

Excel: Use the sample value column with one repeated value and other distinct nonblank values. Returns: 1

Three copies in Sheets

=COUNTA(A2:A100)-COUNTUNIQUE(FILTER(A2:A100, A2:A100<>""))

Google Sheets: Enter A, A, A in A2:A4. Returns: 2

All unique

=COUNTA(A2:A100)-SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100))

Excel: Enter A, B, and C once each in A2:A4. Returns: 0

Common errors and fixes

Issue	Likely cause	Fix
The count is unexpectedly high	Hidden spaces make visually identical values distinct.	Normalize the source values before counting duplicates.
Formula blanks are included	COUNTA counts cells containing formulas even when they display as empty.	Use a helper range that explicitly excludes empty-string results when required.
Excel evaluates slowly	The COUNTIF array scans a large range repeatedly.	Limit the range to the data rows or use a helper frequency column.

When not to use this formula

Do not use this result when you need the number of distinct duplicated values; use a duplicate list instead.

Alternatives

Alternative	When to use it
Find Duplicates Formula	Use when the duplicated values themselves are needed.
Flag Duplicate Values Formula	Use when duplicate rows need visible labels rather than one total.

Related formulas

Official references

UNIQUE function from Microsoft
COUNTUNIQUE function from Google
Use the COUNTIF function in Microsoft Excel from Microsoft

FAQ

What does this count represent?

It counts extra occurrences beyond the first copy, not the number of distinct values that are duplicated.

What does three A values produce?

Three copies contain two extra occurrences, so the result is 2.