# Syntax

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

# Steps

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