Go Back

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:
Here are some problems that our users have asked and received explanations on

I am getting a "0" return on my =COUNTIF feature. I am trying to get a count of unique text values within a given range, removing duplicates, using the formula =COUNTIF(A2:A138,B2), whereas the "B" column is the column containing only the unique text fields.
Solved by T. B. in 25 mins
I'm using the following formula to count the unique values in a range of values. I need the formula to work without having to readjust the ranges every time. =SUMPRODUCT(1/COUNTIF(Data!$A$2:$A$5601,Data!$A$2:$A$5601))
Solved by O. H. in 18 mins
I have a range of cells and would like to use a formula to count the unique values occurring in the range.
Solved by E. B. in 21 mins

Leave a Comment

avatar