19. Excel How to count the number of unique values ?
Solution 1
To count the number of unique values in a range of cells A3:A17 for example,
1 - If the range does not contain any empty cells, use the formula :
=SUMPRODUCT(1/COUNTIF(A3:A17,A3:A17))
2 - If the range can contain empty cells, use the formula :
=SUMPRODUCT((A3:A17<>"")/COUNTIF(A3:A17,A3:A17&""))
Explanations
We are going to explain these formulas step by step and this is an opportunity to recall a certain number of Excel functions.
Let's take the following extract:
The formula in cell B3 is :
=COUNTIF($A$3:$A$17,A3)
This formula counts the number of occurrences of the value in A3 in the range A3:A17. The "$" signs are added for the autofill.
In C3 it is the inverse of the value in B3 which is the formula :
=1/B3
And in cell C18, it is the sum of the values in column C:
=SUM(C3:C17)
The value obtained in cell C18 is the number of unique values in column A, because each unique value is counted by 1.
For example, if a value exists 3 times, we will have in column C for each occurrence :
0,3333333
If we add this value 3 times, we will have 1.
In this way we have calculated the number of unique values, but we can do this without going through an intermediate column. The formula is as shown above:
=SUMPRODUCT(1/COUNTIF(A3:A17,A3:A17))
Here the function COUNTIF has had as 2nd argument a range of cells. In this case the comparison is made with the value of each cell of the range and returns an array of values with as many elements as the number of cells of the range.
The function SUMPRODUCT with one argument gives us the sum of the elements of the given array. These are the same values as those in column C of the previous extract.
To help you understand better, I show you the elements of the array returned by the function COUNTIF, as well as the inverse elements, using the function INDEX.
In cell C3, the formula is :
=INDEX(COUNTIF($A$3:$A$17,$A$3:$A$17),B3)
In cell D3, the formula is :
=INDEX(1/COUNTIF($A$3:$A$17,$A$3:$A$17),B3)
Treatment of empty cells
If we add an empty cell to the range A3:A17, then the expression
COUNTIF(A3:A17,A3:A17)
Will return an array with one element equal to 0. The expression
1/COUNTIF(A3:A17,A3:A17)
will return an array with one element equal to error #DIV/0! and the sum returned by SUMPRODUCT will also be error #DIV/0!
The reason for this is that the comparison with an empty cell in the COUNTIF function gives FALSE. But, we can get around this problem by concatenating the value to be compared with the empty string "", as the comparison of the empty cell with the empty string gives TRUE.
The formula to use is therefore :
=SUMPRODUCT(1/COUNTIF(A3:A17,A3:A17&""))
In this way we avoid the error #DIV/0! But, empty cells will also be counted by 1.
If we do not want to count empty cells, then we have to use the formula :
=SUMPRODUCT((A3:A17<>"")/COUNTIF(A3:A17,A3:A17&""))
Solution 2
To count the number of unique numerical values in a range A3:A17, use the formula :
=SUM(IF(FREQUENCY(A3:A17,A3:A17)>0.1,1,0))
To count the number of unique text or numeric values in the range A3:A17 provided that this range does not contain any empty cells, use the formula :
=SUM(IF(FREQUENCY(MATCH(A3:A17,A3:A17,0),MATCH(A3:A17,A3:A17,0))>0.1,1,0))
To count the number of unique text or numeric values in the range A3:A17, use the formula :
=SUM(IF(FREQUENCY(IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""),IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""))>0.1,1,0))
Explanations
Reminder about the FREQUENCY function :
The FREQUENCY function takes two arguments and returns an array of elements. Let's take the following example:
The 2nd argument is used to define intervals. For the example given above where the 2nd argument is the range B3:B6, the intervals are :
- Values less than or equal to 1
- Values greater than 1 and less than or equal to 3
- Values greater than 3 and less than or equal to 5
- Values greater than 5 and less than or equal to 7
- Values greater than 7
Each of these intervals corresponds to an element of the array returned by the FREQUENCY function and whose value is the number of values of the first argument A2:A16 included in the corresponding interval.
For example, the first element of the array returned is the number of values in the range A2:A16 that are less than or equal to 1.
The last element of the returned array is the number of values in the range A2:A16 that are strictly greater than 7 which is 0.
In the previous example, the values given in the 2nd argument are ordered. What happens if we give the same values in a different order:
Note that we get the same values but in a different order. First the number of values between 3 and 5, then the number of values between 1 and 3.
Another question, if there is a duplicate value in the 2nd argument? In this case the value corresponding to the 2nd occurrence is 0:
Finally, if I give the same range of cells B3:B17 as the 1st and 2nd arguments:
- In front of the value 5, we have the number of occurrences of this value.
- In front of the value 3, we have the number of occurrences of this value.
- In front of the value 4, we have the number of occurrences of this value.
- In front of the 2nd occurrence of the value 5 in row 6, we get 0. And so on.
The result is that we have a number other than 0 in front of the first occurrence of each number.
The following formula counts the number of elements in the array returned by FREQUENCY that are different from 0. These elements are the first occurrences of each number in the list:
=SUM(IF(FREQUENCY(B3:B17,B3:B17)>=1,1,0))
How do you count the number of unique numeric and also text values?
As mentioned above, the formula is :
=SUM(IF(FREQUENCY(MATCH(B3:B17,B3:B17,0),MATCH(B3:B17,B3:B17,0))>=1,1,0))
Compared to the previous formula, I just replaced the address of the range B3:B17 with :
MATCH(B3:B17,B3:B17,0)
La fonction EQUIV retourne la position de la valeur en 1er argument dans la liste donnée en 2ème argument. Si le1er argument est une liste d’éléments, alors la fonction retourne un tableau d’éléments.
The MATCH function returns the position of the value in the 1st argument in the list given in the 2nd argument. If the 1st argument is a list of elements, then the function returns an array of elements.
The expression
MATCH(B3:B17,B3:B17,0)
Returns an array whose elements are the positions of the first occurrences for each value. The following extract shows you the elements of this array:
So the MATCH function has returned numbers which will be processed by the FREQUENCY function in the manner indicated above.
In the presence of an empty value
The MATCH function returns the error #N/A if it receives an empty cell as its first argument.
Therefore, if the range has empty cells, then replace in the formula :
MATCH(B3:B17,B3:B17,0)
By
IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),"")
The final formula for counting numeric and text single values is :
=SUM(IF(FREQUENCY(IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""),IF(LEN(A3:A17)>0,MATCH(A3:A17,A3:A17,0),""))>0.1,1,0))
Reader comments
