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