How to Count Unique 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. 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?


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


=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.

Leave a Reply