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. To figure out the unique count, we will use the COUNTIF function and the SUMPRODUCT function.
The COUNTIF function is to count 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: To count the number of cells that are unique in column B
- COUNTIF(B2:B12, B2:B12) is to count the frequency of each value (e.g., the frequency of 7 is 3).
- 1/COUNTIF(B2:B12, B2:B12) is to transfer the frequency into the fraction (e.g., each 7 will be 1/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 is the steps to get the number of unique values:
|City||Temperature||COUNTIF(B2:B12, B2:B12)||1/COUNTIF(B2:B12, B2:B12)|
Notes: The SUMPRODUCT Function
The SUMPRODUCT function adds all the multiplication results for all arrays.
=SUMPRODUCT(array1, [array2], …)
– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.