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
| Amount | Step 1 | Step 2 | Step 3 |
|---|---|---|---|
| 1,278 | 1 | True | 1 |
| 800 | 2 | False | 0 |
| 800 | 2 | False | 0 |
| 588 | 1 | True | 1 |
| 500 | 1 | True | 1 |
| 300 | 3 | False | 0 |
| 300 | 3 | False | 0 |
| 300 | 3 | False | 0 |
| 200 | 1 | True | 1 |
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.