View original (French)

Excel formula: count the number of different values

Solved
smer38 Posted messages 13 Status Membre -  
 nono -
Hello,

I am trying to count the number of unique values in a column, but I can't seem to do it. Does anyone have an idea?

Thank you.

4 réponses

Réponse 1 / 4
Beaj109562 Posted messages 3 Status Membre 43
 
My comment on 'g's reply from December 3, 2012, at 3:14 PM was deleted with the justification that "The message has been deemed obsolete." However, this conversation does not ultimately clarify what the best solution is! Therefore, I will give my opinion/summary:

The best solution is the formula
[A] {
=SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))
} (the last one proposed in the topic, by tongtong)

It provides accurate counts of distinct values in the cell range for all types of tested values: strings from the alphabet, numbers, and non-alphanumeric characters (+, /, ...), as well as error values (#DIV/0!, #N/A, ...) and logical values (TRUE, FALSE), and even the empty cell value (which is not counted as a distinct value).
Be careful, it can be long to calculate (9 seconds for 50 cells, 15 seconds for 500, 25 seconds for 5000, ... error for 50000)

The second formula suggested, by ccm81, [B] {
=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))
} also works very well, provided there are no empty cells in the search range, or that we specifically want to count this empty cell as a distinct value:
To avoid it returning an error value (#DIV/0!) as soon as a cell is empty, it is best to replace empty cells with a specific value, e.g., 'empty' (or a dot, or even a space but be careful of less distinguishable omissions). The number of distinct values found then does include this "empty" value, which may be desired at times. The same remark applies regarding the calculation length.

Finally, the formula first mentioned by g [C] {
=SUM(IF(COUNTIF(A1:A7,A1:A7)=1,1))
} should be forgotten: it gives inaccurate values as soon as a value is duplicated... One just has to see its count evolve strangely when counting a range of cells containing a single character (it finds 1), then that character twice (it finds 0), then three times (0), ...
55
michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention   3 320 > tontong Posted messages 2575 Registration date   Status Membre Last intervention  
 
Hi tongtong



The origin of the formula comes from English-speaking forums, as Laurent Longre mentioned 10 or 15 years ago: 

. 
.....
"2. Range containing all types of values
=SUM(1/COUNTIF(Range;Range)) 

This formula is derived from English-speaking forums on Excel.
COUNTIF(Range;Range)

returns an array containing the number of repetitions of each value within the specified range. If a value is repeated N times, the corresponding array contains N times the number N.
 By summing the inverses of these numbers, we get N*(1/N), or 1 for each of the duplicated values. The result is the number of unique values in the range.
 Unlike the previous formula, this one counts the number of different values regardless of their type (number, text...) but the range used must not include empty cells, unless it also contains at least one number 0 or we modify it as follows:
=SUM(IF(Range<>"";1/COUNTIF(Range;Range))) "....
2
tontong Posted messages 2575 Registration date   Status Membre Last intervention   1 064 > michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention  
 
Hi Michel
And thank you for this highly documented history.
0