Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum unique (2003)

    I have a calculation (I got it here of course) that counts unique group numbers - =sum(1/countif(range:range,range:range)) "ctrl alt enter" instead of just enter.
    Now I was wondering if there was a way to sum a column that way. Everytime the group number changes, use just one of the numbers in the contract column to sum.
    group number Contracts
    0001 50
    0001 50
    0001 50
    0001 50
    0002 100
    0002 100
    0002 100
    0003 10
    0003 10
    0004
    0004
    0004
    0005 500
    Total 660

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum unique (2003)

    Let's say your data are in A1:B13. The following array formula (confirm with Ctrl+Shift+Enter) will sum unique entries in column B (unique defined by column A):

    =SUM(B1:B13/COUNTIF(A1:A13,A1:A13))

    The only difference with the count formula is that 1 has been replaced with B1:B13 (the range you want to sum).

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum unique (2003)

    Could you simply use Data | Subtotals?
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sum unique (2003)

    Jerry,

    I'm a big fan of subtotals, but how would that help to sum distinct entries only?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum unique (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Good point
    Jerry

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum unique (2003)

    Thanks, I haven't had a chance to test it but I am sure it will work fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •