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

If you need to count the number of unique values in a range of cells (named "data" in the example below), you can use a formula that uses COUNTIF and SUMPRODUCT.

Working from the inside out, COUNTIF looks inside the **data** range and counts the number of times that each individual value appears in **data**. The result is an array or numbers, that might look something like this: {1,2,2,3,3,3}.

After COUNTIF is finished the results are used as a divisor with 1 as the numerator. Values that appear in **data** once appear in the array as 1, but values that appear multiple times will appear as fractional values that correspond to the mutliple. (i.e. a value that appears 5 times in data will generate 5 items in the array with a value of 1/5 = .2).

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

If data might contain blank cells, you need to adjust the formula as follows:

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

The data&"" expression prevents zeros from ending up in the array created by COUNTIF when there are blank cells in **data**. It does this by ensuring that the criteria for an empty cell is "", and not zero. This is important, because a zero in the divisor will throw a #DIV/0 error. So this version of the formula won't throw an error when there are blank cells, but it *will* include blank cells in the count. If you want to exclude blank cells in the count, use:

=SUMPRODUCT((data<>"")/COUNTIF(data,data&""))

This is a cool and elegant formula, but it calculates much more slowly than formulas that use FREQUENCY to count unique values. For larger data sets, you may want to switch to a formula based on the FREQUENCY function. Here's a formula for numeric values, and one for text values.