Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sum 3 largest count values (Excel 2000>)

    Is it possible to do this?

    In the attached, I can sum the 3 largest values... 6+6+5..., but I actually want to sum 6+5+4 (3 largest "count" values).
    Any ideas?
    Regards,
    Rudi

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

    Re: Sum 3 largest count values (Excel 2000>)

    From your example, I assume that you mean the three largest unique values.
    See the attached workbook. The largets value is calculated by a simple MAX formula, the second and third largest by an array formula.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum 3 largest count values (Excel 2000>)

    You assume correctly...
    This is good...many tx! I cant believe how knowledgable you are in these things!!!

    PS: Is it possible to design this into ONE nested function. I always prefer nest functions above aditional columns of intermediate functions, which I have to hide!
    Regards,
    Rudi

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

    Re: Sum 3 largest count values (Excel 2000>)

    Here you go. Try and figure it out! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    <code>
    =SUM(LARGE(IF(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-MIN(ROW(A2:A11))+1,A2:A11,""),{1,2,3}))
    </code>
    You must enter it as an array formula, i.e. confirm with Ctrl+Shift+Enter.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum 3 largest count values (Excel 2000>)

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    I'll cheat and use the Evaluate formula dialog!!

    PS: This is a four nest function? Correct!
    Regards,
    Rudi

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

    Re: Sum 3 largest count values (Excel 2000>)

    Yes:
    <pre>=SUM(LARGE(IF(MATCH(A2:A11,A2:A11,0)=ROW(A2:A 11)-MIN(ROW(A2:A11))+1,A2:A11,""),{1,2,3}))
    '0 1 2 3 3 3 4
    </pre>

    The second ROW function is 4 levels deep counting from the top-level SUM function.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum 3 largest count values (Excel 2000>)

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    I'm getting there....
    Regards,
    Rudi

Posting Permissions

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