Count unique values Excel



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