sum top 18
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: sum top 18

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hopefully this is a quick one for some one.
    I have a list of 25 numbers and i would like the sum the top 18 numbers in one cell. Is there a formula something like sumif etc which cud do this please.

    Thanks a lot

    Jonty

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    jonty
    try =SUM(LARGE(A1:A50,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18})). adjust cell refs to suit.

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-01 19:28, jonty wrote:
    Hopefully this is a quick one for some one.
    I have a list of 25 numbers and i would like the sum the top 18 numbers in one cell. Is there a formula something like sumif etc which cud do this please.

    Thanks a lot

    Jonty
    Hi Jonty,

    WIth your data in A1:A25, try:

    { =SUM(LARGE(A1:A25,ROW(1:18))) }

    This must be entered as an array formula with Ctrl+Shift+Enter rather than just Enter.

    If array entered, Excel will place the braces {} around the formula you type.

    You can also name a formula and place the above in the 'refers to' area. It does not have to be array entered if you use this method.

    Regards,
    Jay


    [ This Message was edited by: Jay Petrulis on 2002-04-01 20:00 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks very much - both solutions are working brilliantly

    Cheers
    Jonty

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi jonty


    If you would like a more flexible solution just the the SUBTOTAL fumction on you data with Auto filters showing the top 18 (or whatever) This can be be found under Top Ten of the Auto filter.



User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com