Count unique values in a range with COUNTIF

This array is configured as a divisor with 1 as the numerator.

a value that appears 4 times in data will generate 4 values = 0.25).

Finally, the SUMPRODUCT function sums all values in the array and returns the result.

It works because using an empty string ("") for criteria will count empty cells.

Slow Performance?

For larger data sets, you may want to switch to a formula based on the FREQUENCY function.

Here’s a formula fornumeric values, and one fortext values.

COUNTIF can be used to count…