How to Sum Cells Without Duplicates

Unlike the unique values, the cells without duplicate values are those numbers that only appear once. You can combine the COUNTIF and SUMPRODUCT functions to determine the number of cells without duplicate salaries.

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 without duplicates in column B?

=SUMPRODUCT(--(COUNTIF(B2:B10, B2:B10)=1), B2:B10)

The result returns 2,566. There are four values without duplicates: B2 (1,278), B5 (588), B6 (500) and B10 (200).

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 equals 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 × 1 + 800 × 0 + 800 × 0 + 588 × 1 + 500 × 1 +300 × 0 + 300 × 0 + 300 × 0 + 200 × 1 = 2,566
AmountStep 1Step 2Step 3
1,2781True1
8002False0
8002False0
5881True1
5001True1
3003False0
3003False0
3003False0
2001True1

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.

Leave a Reply