View original (French)

Sum of cells without duplicates

Solved
xeug Posted messages 36 Status Member -  
xeug Posted messages 36 Status Member -
Hello,
In one column, I have order numbers representing the orders.
10;11;12;12;13;14...
The problem is that I can have two identical order numbers representing different items on the same order.
I would like to know the number of orders (in the example above, 5 and not 6).
Thank you.

2 answers

Answer 1 / 2
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello
the order number in A1:A30

=SUMPRODUCT(1/COUNTIF(A1:A30,A1:A30))
Array formula to be validated by "ctrl+shift+enter" (and not directly by "enter"), the blinking cursor in the formula bar. The formula is then automatically enclosed in braces: { formula }

--
Michel
0
Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 783
 
Hello Michel,

Good point, but the PROD is not necessary:
=SUM(1/COUNTIF(A1:A30,A1:A30))
in array form works too.

Best regards
Patrice
0
xeug Posted messages 36 Status Member 12 > Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention  
 
So many solutions...
And it works
Thank you.
0