How to Count Unique Numeric Values in Excel

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

=SUMPRODUCT(1/COUNTIF(B2:B12,B2:B12))

  • 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).

In the example, there are eight unique values in column B.

The following table is the steps to get the number of unique values:

CityTemperatureCOUNTIF(B2:B12, B2:B12)1/COUNTIF(B2:B12, B2:B12)
New York-511
Los Angeles-1820.5
Chicago1511
Houston730.333333333
Phoenix1311
Philadelphia-1511
San Antonio730.333333333
San Diego730.333333333
Dallas1811
San Jose-1820.5
Austin-411

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.

Leave a Reply