+ Reply to Thread
Results 1 to 15 of 15

Need help writing a formula to sum only highest numbers in lists

  1. #1
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Need help writing a formula to sum only highest numbers in lists

    Hello all

    I am looking for help with a formula, here's the situation. I have a list of competitors and their dogs that earn points in competitions that need to be tracked for the year. Currently, the standings are based on the total points earned by each team, but I would like to set up a scenario where I can see standings based only on the sum of a certain number of each team's top scores (either 5, 10, or 15).

    I have formulas in place to sum up the number of competitions that each team has earned points in, as well as their total points earned for the year, but I don't know how to write the formula to add up only the top 5, 10 or 15 sets of points for each team. Is this possible?

    I've attached my worksheet with my current progress.

    Thank you

    Cross posted in Excel guru - https://www.excelguru.ca/forums/show...a&goto=newpost
    Attached Files Attached Files
    Last edited by 57 Ranch; 01-21-2021 at 05:17 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,473

    Re: Need help writing a formula to sum only highest numbers in lists

    Administrative Note:

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    19,177

    Re: Need help writing a formula to sum only highest numbers in lists

    It would be very helpful if you explained what you are attempting to do if you were to do this manually and also, for the example mock up what the solution should look like. I am not understanding your needs.
    Last edited by alansidman; 01-21-2021 at 07:05 PM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,705

    Re: Need help writing a formula to sum only highest numbers in lists

    Echo Alan.

    This yields similar to your upload, but I can not account for all the blank cells you expect.

    In F2 across and down column H
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,032

    Re: Need help writing a formula to sum only highest numbers in lists

    Formula for F2
    =IF(SUM(IF($A$1:$A2&$B$1:$B2=$A2&$B2,1,0))=1,SUM(LARGE(IF($A$2:$A$2000&$B$2:$B$2000=$A2&$B2,$C$2:$C$2000,0),ROW(INDIRECT("A$1:A" & F$1)))),"")

    Fill down and fill right


    Or Maybe


    =IF(AND(SUM(IF($A$1:$A2&$B$1:$B2=$A2&$B2,1,0))=1,SUM(IF($A$1:$A2000&$B$1:$B2000=$A2&$B2,1,0))>F$1-5),SUM(LARGE(IF($A$2:$A$2000&$B$2:$B$2000=$A2&$B2,$C$2:$C$2000,0),ROW(INDIRECT("A$1:A" &F$1)))),"")
    Last edited by mehmetcik; 01-21-2021 at 07:06 PM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  6. #6
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Re: Need help writing a formula to sum only highest numbers in lists

    I want to add all the points accumulated by each team, but only use their 5 (or 10 or 15) highest scores to do that.
    The lower scores would not be used, and if a team had less than that number of scores recorded, then only the available ones are used.

    For example, Team A has 13 scores in competitions, but only 10 are going for their total. Team B has only 8 scores, so all of them have to be used for their year-end total.

    I don't have an example of it yet, still working on it manually

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,497

    Re: Need help writing a formula to sum only highest numbers in lists

    In F2 then copied across
    ARRAY formula

    =IFERROR(IF($C2>=LARGE(IF(($A$2:$A$391=$A2)*($B$2:$B$391=$B2),$C$2:$C$391,""),F$1),$C2,""),$C2)

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    01-09-2014
    Location
    Leeds, England
    MS-Off Ver
    Excel 365
    Posts
    50

    Re: Need help writing a formula to sum only highest numbers in lists

    I have attached my solution.

    I believe some of the above solutions would return an incorrect result if, say, the 5th and 6th best scores were the same. They would add up the top 6 rather than the top 5 in that case.

    I have used a separate table to record the 1st, 2nd, 3rd etc. best scores, then ranked these and shown the top 10 teams by each measure.

    I hope this does what you need.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Re: Need help writing a formula to sum only highest numbers in lists

    I'll give these all a try and see what I come up with. Thank you!
    Last edited by 57 Ranch; 01-22-2021 at 05:48 PM.

  10. #10
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Re: Need help writing a formula to sum only highest numbers in lists

    Quote Originally Posted by FlameRetired View Post
    Echo Alan.

    This yields similar to your upload, but I can not account for all the blank cells you expect.

    In F2 across and down column H
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Yes, that works beautifully. And I can sort to leave all the blanks cells at the bottom, then paste values for my report. Thank you!

  11. #11
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Re: Need help writing a formula to sum only highest numbers in lists

    Quote Originally Posted by kvsrinivasamurthy View Post
    In F2 then copied across
    ARRAY formula

    =IFERROR(IF($C2>=LARGE(IF(($A$2:$A$391=$A2)*($B$2:$B$391=$B2),$C$2:$C$391,""),F$1),$C2,""),$C2)

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Thank you for your help! Your formula doesn't quite do what I want, but I surely do appreciate your help. I can do basic formulas, but the higher ones are beyond me

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,473

    Re: Need help writing a formula to sum only highest numbers in lists

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  13. #13
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Re: Need help writing a formula to sum only highest numbers in lists

    Quote Originally Posted by mehmetcik View Post
    Formula for F2
    =IF(SUM(IF($A$1:$A2&$B$1:$B2=$A2&$B2,1,0))=1,SUM(LARGE(IF($A$2:$A$2000&$B$2:$B$2000=$A2&$B2,$C$2:$C$2000,0),ROW(INDIRECT("A$1:A" & F$1)))),"")

    Fill down and fill right


    Or Maybe


    =IF(AND(SUM(IF($A$1:$A2&$B$1:$B2=$A2&$B2,1,0))=1,SUM(IF($A$1:$A2000&$B$1:$B2000=$A2&$B2,1,0))>F$1-5),SUM(LARGE(IF($A$2:$A$2000&$B$2:$B$2000=$A2&$B2,$C$2:$C$2000,0),ROW(INDIRECT("A$1:A" &F$1)))),"")
    Thank you! The first formula works great for what I need to create a report.

  14. #14
    Registered User
    Join Date
    07-08-2018
    Location
    Yoder, WY
    MS-Off Ver
    Office 365 online
    Posts
    17

    Re: Need help writing a formula to sum only highest numbers in lists

    Quote Originally Posted by SimonLock View Post
    I have attached my solution.

    I believe some of the above solutions would return an incorrect result if, say, the 5th and 6th best scores were the same. They would add up the top 6 rather than the top 5 in that case.

    I have used a separate table to record the 1st, 2nd, 3rd etc. best scores, then ranked these and shown the top 10 teams by each measure.

    I hope this does what you need.
    Thank you, I'll give it a try

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,705

    Re: Need help writing a formula to sum only highest numbers in lists

    You are welcome.
    Thank you for the feedback and marking your thread Solved.
    Last edited by FlameRetired; 01-26-2021 at 01:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF formula that will rank numbers highest to lowest and then score them
    By slock92 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 07:13 PM
  2. [SOLVED] Formula like MAX to find the highest value (contains text and numbers)
    By Nero_slk in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-08-2016, 03:50 AM
  3. Help with writing a formula to calculate based on a range of numbers
    By hapahoale in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-25-2016, 10:24 AM
  4. Using highest of 2 numbers in formula
    By pdns in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2014, 05:18 PM
  5. Replies: 2
    Last Post: 02-24-2011, 10:18 AM
  6. [SOLVED] Excel Formula - to calculate highest number from a set of numbers
    By Emily in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2006, 01:55 AM
  7. Replies: 1
    Last Post: 01-09-2006, 09:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1