Count unique values in a range with COUNTIF

General Formula 

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

Explanatory Notes

With the Excel COUNTIF function alongside the SUMPRODUCT function, you can easily count the number of unique values or items within a range of cells in an array or table

How the Excel Count Unique Values in a Range with COUNTIF Function works

The Excel COUNTIF function begins work from the background, then to the exterior, the COUNTIF function observes a particular data set and counts the occurrence of individual value within the data. The resulting array of numbers might look similar to this: {30;30;30;21;21;30;31;31;12;12} or {Joe;Joe;Joe;Kim;Kim;Jeff;Jeff}.

Once The Excel COUNTIF function is through with the counting of unique values or items, the results are then used to function as a divisor with 1 serving as the numerator. The values within the range that appear once will appear in the data set as 1, but values that appear more than once will be displayed as fractions that match the divisible factor. (e.g., a value that appears 7 times in the range will generate 7 items in the array of values with a value of 1/7 = 0.14).

The SUMPRODUCT function finally comes in to add up all the values within the data set and displays the final result.

Example 1

For the example below, use the formula:

=SUMPRODUCT(1/COUNTIF(B5:B13,B5:B13))

To count the number of values that are unique to each other

Figure 1: Example on how to use Counting Unique Values in a Range with COUNTIF function to count values that are unique to each other

Example 2

The formula below

=SUMPRODUCT(1/COUNTIF(B5:B13,B5:B13))

Counts the frequency of occurrence for unique values  

Figure 2: Example on how to use Counting Unique Values in a Range with COUNTIF function to count the frequency of unique values

Managing blank cells with the Excel COUNTIF Function

If your data range contains cells that are blank, all you need do is to modify the formula to an extent as seen below:

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

Where data&”” expression stops zeros from being in the created array by the COUNTIF function blank cells exist within the data range. This is done by making sure that the benchmark for counting any blank cell is “”, but not a zero (which is a figure). This is necessary to be done because when there is a zero in the divisor, it will flag #DIV/0 error. On the other hand, this form of this formula below will not flag an error whenever there are empty cells, but it will count the empty cells as well. To avoid such inclusion of empty cells during counts, use the formula below will be helpful:

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

Is it taking lots of time?

This complexity of this formula makes it calculate at a slow pace than other formulas that utilize FREQUENCY to know the amount of all the unique values. If you are handling a large set of data, you can decide to change to another formula that utilizes the FREQUENCY function for quicker results.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar