How to Count Numbers with Duplicates

When working with data, it is common to have duplicate values in a range. Please check here to count the number of unique values in a range, or here to count cells without duplicates.

To calculate the numbers only with duplicates, you can use the unique count minus the number of cells without duplicates.

Question: How many numbers are duplicates in column C?

=SUMPRODUCT(1/COUNTIF(C2:C12, C2:C12)) - SUMPRODUCT(--(COUNTIF(C2:C12, C2:C12)=1))
=SUMPRODUCT(1/COUNTIF(C2:C12, C2:C12)) - SUMPRODUCT((COUNTIF(C2:C12, C2:C12)=1)*1)

The result returns 3, so three numbers are duplicates in column C.

Explanation: 

  • Step 1: SUMPRODUCT(1/COUNTIF(C2:C12, C2:C12)): To find the number of unique values in the range;
  • Step 2: SUMPRODUCT((COUNTIF(C2:C12, C2:C12)=1)*1): To find the number of values without duplicates;
  • Step 3: The difference will be the number of cells with duplicate values in the range.

To figure out the number of cells with duplicate values, you can use the following formula:

=COUNT(C2:C12) - SUMPRODUCT(--(COUNTIF(C2:C12, C2:C12)=1))
=COUNT(C2:C12) - SUMPRODUCT((COUNTIF(C2:C12, C2:C12)=1)*1)

The result returns 6, so six cells are duplicates.

Notes: The SUMPRODUCT Function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required; the first array is to multiply and then add.
– Array2 is optional; the second array is to multiply and then add.

Leave a Reply