Excel formula: Count unique values in a range with COUNTIF

How to Count Unique Values in a Range with COUNTIF

To count the number of unique values in a range of cells in excel, we need to use a formula that is based on the COUNTIF as well as the SUMPRODUCT functions. This article provides an elaborate guide on how to go about counting the number of unique values in a range with the COUNTIF function.

Figure 1: How to count unique values in a range with COUNTIF

General syntax of the formula

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

Where;

Data-refers to the range of cells from where you want to count the unique values.

How this formula works

Let us consider how this formula works by studying it from the inside out;

The COUNTIF function

The formula has the COUNTIF function which looks inside the data range and counts the number of times that each of the values appear in the range. This function returns the result in an array form.

The result from the COUNTIF function is then used as a divisor with 1 as the numerator. Here, those data that appear only ones will appear in the array as 1, given that 1 divide by 1 is 1. Those values that appear more than ones will appear in the array as a fraction, which corresponds to the number of times it has appeared.

SUMPRODUCT

This function will then sum up all the values in the array and return the result.
What if we have blank cells within the range of data?
The formula above might not be appropriate when handling a range of values that might have blank cells.
To get the correct count, we need to adjust the formula and make it look like the one below;

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

Figure 2: How to count unique values in a range with blank cells

In the figure above, we have use the sign “&” to prevent zeros from being created in the array by the COUNTIF function. Here, we set the criteria for all the cells whose value is “ “ is not zero.

Note that having a zero in the divisor will return a #DIV/0 error. This formula also includes the blank cells in the count.

If you want to exclude the blank cells we need to modify the formula to look as the one below;

=SUMPRODUCT((data<> “”)/COUNTIF(data, data & “”))

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

I have a column of data with nonunique values in it. I want to count the number of times within the column that each value appears. I have a second column with only the unique values present, as a means of referencing individual values. How can I format the COUNTIF function to keep the range for the column static, while having the characteristic increasing reference for the unique value (which the function is counting) increase?

An Excelchat Expert solved this problem in 25 mins!

I would like to use a formula to provide a count of unique values in a range that occur at least 25 times.

An Excelchat Expert solved this problem in 20 mins!

I am trying to count 200 different unique values across a spreadsheet of roughly 200 columns and 5000 rows. Countif only works for single columns, I need a formula to count across an entire sheet.

An Excelchat Expert solved this problem in 28 mins!

Post your problem and you'll get expert help in seconds