Duplicates are those numbers appear more than once in a data range. You can combine the COUNTIF and SUMPRODUCT functions to calculate the sum of cells with duplicates.
Example: You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.
Questions: What is the sum of the values with duplicates in column B?
=SUMPRODUCT(–(COUNTIF(B2:B10, B2:B10)>1), B2:B10)
The result returns 2,500. There are five cells having duplicates: B3 (800), B4 (800), B7 (300), B8 (300) and B9 (300).
Explanation:
- Step 1: COUNTIF(B2:B10, B2:B10): To find the frequency of each number;
- Step 2: (COUNTIF(B2:B10, B2:B10)>1): If the frequency of the number is greater than one, the result returns TRUE; otherwise, it returns FALSE;
- Step 3: (–(COUNTIF(B2:B10, B2:B10)>1)): The double hyphen converts TRUE into one and FALSE into zero;
- Step 4: The SUMPRODUCT function returns the sum of the products of the new array and the values in column B.
- Formula: 1,278 × 0 + 800 × 1 + 800 × 1 + 588 × 0 + 500 × 0 +300 × 1 + 300 × 1 + 300 × 1 + 200 × 0 = 2,500
| Amount | Step 1 | Step 2 | Step 3 |
|---|---|---|---|
| 1,278 | 1 | False | 0 |
| 800 | 2 | True | 1 |
| 800 | 2 | True | 1 |
| 588 | 1 | False | 0 |
| 500 | 1 | False | 0 |
| 300 | 3 | True | 1 |
| 300 | 3 | True | 1 |
| 300 | 3 | True | 1 |
| 200 | 1 | False | 0 |
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 add.
– Array2 is optional; the second array is to multiply and add.