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

  1.
    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
    1. 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
      1. 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