Count the Unique Values in a Range
(page 2 of 2)
A Shorter But More Complicated Solution
In the first solution, we created a column just to index the unique names in the Agent column. We then enter a COUNTIF function in H4 to return the number of individual agents.
Now we are going to look at a more direct solution. In cell J4, enter the following formula:
=SUMPRODUCT(1/COUNTIF($A$4:$A$52,$A$4:$A$52))
The COUNTIF($A$4:$A$52,$A$4:$A$52) part of the formula builds an array of the number of occurrences of each name in the Agent column. There are 8 occurrences of Jerry Seinfeld, 16 of Elaine Benes, 12 of George Costanza, 7 of Newman, and 6 of Cosmo Kramer.
If we divide the 8 occurrences of Jerry Seinfeld by 1, we get 0.125. Dividing the 16 occurrences of Elaine Benes gives us 0.0625. Dividing the 12 occurrences of George Costanza gives us .08333, and so on.
Now Jerry Seinfeld, with the value of 0.125 is repeated 8 times in the range which sums to 1. Elaine Benes, with the value of 0.0625, is repeated 16 times in the range and sums to 1. And so on. For each unique name, 1 is returned. The sum of these values equals the count of unique items. When we enter the above formula in cell J4, the value of 5 is returned. We have 5 different names in the Agents column.
There is only one problem remaining. What happens if we have a blank cell in our range? Each blank cell would return a 0. Dividing 1 by a cell that is empty or one containing a zero will return a #DIV/0! Error. To prevent this error, we need to count the empty cells also and not return a zero.
By adding &"" to the end of the COUNTIF, the formula will count the blank cells:
=SUMPRODUCT(1/COUNTIF(A4:A52,A4:A52&""))
However, blanks cells will now be counted as a value. We can resolve this problem by testing for blank cells. Instead of dividing the array of counts into 1 each time, we will add a logical test which will return a value of TRUE or FALSE. A value of TRUE is equivalent to 1. The cells that are not blank will return a value of TRUE so that 1 will be divided by the array of counts of each unique item. The cells that are blank will return a value of FALSE, so our formula will not count them. The SUMPRODUCT will then return the sum of the arrays of each unique item.
Therefore, let’s change the “1” in our formula to “(A4:A52<>"").” Replace the formula in J4 with the following formula:
=SUMPRODUCT((A4:A52<>"")/COUNTIF(A4:A52,A4:A52&""))
This formula will return the number of unique values in the Agent column and you won't get an error value if there is a blank cell in your range.