When working with a database, it is common to have duplicated values in the working sheet, and you may need to count the unique values in a range. You can use the COUNTIF and SUMPRODUCT functions to determine the unique count.
The COUNTIF function counts the number of cells that meet a criterion, and the SUMPRODUCT function returns the product of numbers in an array.
Example: In the following database, the city is in column A, and the temperature is in column B.
Formula: How many unique values are in column B?
=SUMPRODUCT(1/COUNTIF(B2:B12,B2:B12))
- Step 1: COUNTIF(B2:B12, B2:B12) is to count the frequency of each value (e.g., the frequency of 7 is 3);
- Step 2: 1/COUNTIF(B2:B12, B2:B12) is to transfer the frequency into the fraction (e.g., each seven will be 1/3);
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array.
In the example, there are eight unique values in column B.
The following table shows the steps to get the number of unique values:
| City | Temperature | Step 1 | Step 2 |
|---|---|---|---|
| New York | -5 | 1 | 1 |
| Los Angeles | -18 | 2 | 0.5 |
| Chicago | 15 | 1 | 1 |
| Houston | 7 | 3 | 0.333333333 |
| Phoenix | 13 | 1 | 1 |
| Philadelphia | -15 | 1 | 1 |
| San Antonio | 7 | 3 | 0.333333333 |
| San Diego | 7 | 3 | 0.333333333 |
| Dallas | 18 | 1 | 1 |
| San Jose | -18 | 2 | 0.5 |
| Austin | -4 | 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 then add.
– Array2 is optional; the second array is to multiply and then add.