This article shows how to count unique values Excel formulas SUMPRODUCT and COUNTIF. The SUMPRODUCT function can handle arrays without actually using array formulas, and this allows evaluating the formulas in each cell of an array.

Syntax

=SUMPRODUCT( 1 / COUNTIF( data, data ) )

Steps

1. Start with SUMPRODUCT function =SUMPRODUCT(
2. Type "1/" to divide 1 by next function 1/
3. Continue with COUNTIF function COUNTIF(
4. Use the data range for both arguments of COUNTIF D3:D10,D3:D10
5. Close both functions ))
6. Press the ENTER key to finish the formula.

How

The idea behind the SUMPRODUCT – COUNTIF combination revolves around two things:

1. The SUMPRODUCT function is capable of working with arrays and this allows other functions to return arrays instead of single values.
2. Making every repeating value a fraction of its occurrence, by dividing 1 by their counts.

The COUNTIF function returns the count of values based on a criteria. Using a range (an array of cells and values) to create a criteria makes the function calculate for all cells in the criteria range. As a result, the return value becomes an array instead of a single value. For example,

COUNTIF(D3:D10,D3:D10) returns {3;3;3;2;2;1;2;2}

Each unique item repeats as many times as the count limit (e.g. count 3 for 3 times, count 2 for 2 times …). Therefore, dividing 1 by the result and adding the fractions for each unique item returns 1.

1/COUNTIF(D3:D10,D3:D10) returns {0.33;0.33;0.33;0.5;0.5;1;0.5;0.5}

Finally, we use the SUMPRODUCT to sum up the all values to return the count of unique items.