Planning and Insight Wiki


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


  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.


The idea behind the SUMPRODUCTCOUNTIF 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}