+ 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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    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 Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    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.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  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, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    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
    72

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    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
    Office 365 v 2403
    Posts
    13,396

    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