What is the formula to count duplicates in excel

Answer

To count duplicates in Excel, you can use several methods depending on whether you want to count duplicates in a single column, across multiple columns, or highlight them.

Method 1: Using COUNTIF to Count Duplicates in a Single Column

A cell can be counted as a duplicate if it appears more than once in the column.

1. Suppose your data is in column A from A2 to A20.

2. In cell B2, enter the formula:

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

3. Press Enter.

4. Drag the formula down from B2 to B20.

Explanation:

• COUNTIF counts how many times the value in A2 appears in the range A2:A20.

• If the result is greater than 1, it indicates a duplicate.

Example:

• If A2 = "Apple" and "Apple" appears 3 times in column A, B2 will show 3.

Method 2: Count Only Duplicate Entries

To count only values that appear more than once:

1. Use the formula:

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

2. This marks each cell as "Duplicate" if it occurs more than once.

Method 3: Count Total Number of Duplicates in a Column

To find how many values are repeated (count total duplicate entries):

1. Use this formula:

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

2. Press Enter.

Explanation:

• COUNTIF(A2:A20, A2:A20) creates an array counting occurrences of each value.

• >1 identifies duplicates.

• SUMPRODUCT totals them while avoiding double-counting.

Method 4: Highlight Duplicates Using Conditional Formatting

1. Select the range of cells you want to check for duplicates.

2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

3. Choose a formatting style (e.g., red fill) and click OK.

Note:

• Conditional formatting only highlights duplicates. It does not count them.

Method 5: Advanced - Count Duplicates Across Multiple Columns

1. Suppose the data is in A2:B20.

2. Use this formula to count duplicates across two columns:

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

3. This counts rows where both column A and column B values match the current row.

Tips

• Excel functions like COUNTIF and COUNTIFS are case-insensitive.

• For case-sensitive duplicate counts, use SUMPRODUCT with EXACT:

  =SUMPRODUCT(--EXACT(A2, A$2:A$20))

This approach ensures precise identification and counting of duplicates in any dataset.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report