# Count unique values in a range with COUNTIF

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

### How the formula works

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 of numbers that might look something like this: {3;3;3;2;2;3;3;3;2;2}.

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.

### Handling blank cells

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&""))

### Slow Performance?

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.

## Excel Formula Training

Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!