The unique value is also called distinct value. When a value appears more than once in a data range, it is only counted once.
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.
Question: What is the Sum of Unique Values in Column B?
In this example, some values in column B are duplicates and appear more than once, e.g., 300. If a number appears more than once, you count them once.
Question: What is the Sum of Unique Values in column B?
=SUMPRODUCT(1/COUNTIF(B2:B10, B2:B10), B2:B10)
The result returns 3,666, and there are six unique values in the column B: 1,278, 800, 588, 500, 300 and 200.
Explanation:
- Step 1: COUNTIF(B2:B10, B2:B10): To find the frequency of each number;
- Step 2: 1/COUNTIF(B2:B10, B2:B10): To convert the frequency into fractions. If a number appears three times, each is ⅓;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array and the values in B2:B10.
- Formula: 1,278 × 1 + 800 × 1/2 + 800 × 1/2 + 588 × 1 + 500 × 1 +300 × 1/3 + 300 × 1/3 + 300 × 1/3 + 200 × 1 = 3,666
| Salary | Step 1 | Step 2 |
|---|---|---|
| 1,278 | 1 | 1 |
| 800 | 2 | 1/2 |
| 800 | 2 | 1/2 |
| 588 | 1 | 1 |
| 500 | 1 | 1 |
| 300 | 3 | 1/3 |
| 300 | 3 | 1/3 |
| 300 | 3 | 1/3 |
| 200 | 1 | 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.
