Count Duplicate Values in Excel Column

This guide shows you how to count duplicate values in an Excel column, returning the number of times each value appears or the total number of duplicate entries. You’ll learn the exact formulas for both approaches, how to adapt them for multiple columns, and how to avoid common pitfalls.

Formula

The core formula to count how many times a specific value appears in a column is:

=COUNTIF(range, criteria)

Where:

• range – the column or cell range you are checking (e.g., A2:A100).
• criteria – the value you want to count. It can be a cell reference (e.g., A2) or a literal value (e.g., “apple”).

To count total duplicate entries (every occurrence beyond the first), use:

=SUMPRODUCT((COUNTIF(range, range)>1)*1)

This works by counting each value in the range, checking if that count is greater than 1, and summing the results. For example, if a value appears 3 times, it contributes 3 to the total, not 2.

To count only the number of distinct values that have duplicates (i.e., unique duplicated values), use:

=SUMPRODUCT((COUNTIF(range, range)>1)/COUNTIF(range, range))

This divides each occurrence by its total count, so a value appearing 3 times contributes 1/3 + 1/3 + 1/3 = 1.

Worked Example

Suppose you have a column with the values: 1, 2, 2, 3, 3, 3. Using the default from the calculator above:

1. Count each value: 1 appears 1 time, 2 appears 2 times, 3 appears 3 times.
2. Total duplicate entries = (1-1) + (2-1) + (3-1) = 0 + 1 + 2 = 3.
3. Unique duplicated values = 2 (the values 2 and 3).

The calculator above gives the same results: 3 total duplicate entries and 2 unique duplicated values.

When to Use Each Formula

Choose the formula based on what you need to report:

• Total duplicate entries – use when you want to know how many rows are part of a duplicate set. For example, in a customer list, this tells you how many rows are redundant.
• Unique duplicated values – use when you want to know how many distinct items appear more than once. For example, how many products have multiple orders.
• Per-value counts – use COUNTIF with a specific criteria to see the frequency of a single value, often as a helper column.

How to Apply the Formulas in Excel

1. Open your spreadsheet and select the column with your data.
2. Click an empty cell where you want the result.
3. Type the formula, replacing range with your actual range (e.g., A2:A100).
4. Press Enter. For array formulas (like SUMPRODUCT), Excel handles them automatically in modern versions.

If you need to count duplicates based on multiple columns (e.g., first and last name), combine them into a helper column with =A2&” “&B2, then apply the formula to that helper column.

Common Mistakes and How to Avoid Them

• Counting the first occurrence – The total duplicate formula counts every occurrence, including the first. If you want only the extra copies, use =SUMPRODUCT((COUNTIF(range, range)>1)*(COUNTIF(range, range)-1)).
• Blank cells – COUNTIF counts blank cells as duplicates. Exclude them by using =SUMPRODUCT((range<>“”)*(COUNTIF(range, range)>1)).
• Text vs. numbers – Excel treats “1” and 1 as different. Ensure your data is consistently formatted.
• Case sensitivity – COUNTIF is not case-sensitive. For case-sensitive counting, use SUMPRODUCT with EXACT.

Alternatives to Formulas

If you prefer not to write formulas, Excel offers built-in tools:

• Conditional Formatting – Highlight duplicate values visually, then count them manually or with a filter.
• Remove Duplicates – This deletes duplicates, but you can first copy the column to a new sheet and compare the row counts.
• PivotTable – Drag the column to Rows and Values; the Values area shows counts per item, and you can filter for counts > 1.

Limitations and Performance

These formulas work well for up to tens of thousands of rows. On very large datasets (hundreds of thousands of rows), array formulas may slow down. In that case, consider using Power Query (Get & Transform) to group and count, which is more efficient.

Frequently Asked Questions

How do I count duplicates in a column but only show values that appear more than once?

Use the unique duplicated formula =SUMPRODUCT((COUNTIF(range, range)>1)/COUNTIF(range, range)). It returns the number of distinct values that have duplicates. To list them, use Advanced Filter with “Unique records only” and then compare.

How do I count duplicates in Excel without counting the first occurrence?

Use =SUMPRODUCT((COUNTIF(range, range)>1)*(COUNTIF(range, range)-1)). This subtracts 1 for each duplicated value, so a value appearing 3 times contributes 2.

Can I count duplicates based on two columns?

Yes. Create a helper column that concatenates the two columns with a separator, e.g., =A2&”-“&B2, then run the duplicate count on that helper column.

Why does my COUNTIF formula return 0 for text values?

Check for leading or trailing spaces. Use =TRIM() to clean the data, or use wildcards like =COUNTIF(range, “*”&criteria&”*”) for partial matches.

Is there a way to count duplicates in Google Sheets?

Yes, the same formulas work in Google Sheets. For large data, you can also use the COUNTIF and SUMPRODUCT functions, or the newer QUERY function.

Related

• Manhattan in Control Resonant: Charge Central Checkpoint
• ESP32 Dual Core Scheduling: How to Use Both Cores
• Beats Solo 4 Controls: Buttons & Mic Guide

in values | Values (comma-separated) | - | 1,2,2,3,3,3 | 0
out total_dupes | Total duplicate entries | - | sum(if(count(values, values)>1,1,0)) | 0
out unique_dupes | Unique duplicated values | - | sum(if(count(values, values)>1,1,0)) | 0