Create multiple formulas for one cell to top 5 a summed range of values

  • Newer to exploring formulas in Excel... is there a way to combine functions that would allow me to sum a range of values and then top 5 those sums, including future data being input?

    Specifically looking at column H "Pretax" - needing to do 2 separate outputs

    1. Sum total of 12 lines consecutively (Jan-Dec of a given year) and then top 5 from all current/possible lines
    2. Sum total of 6 lines, skip 6 lines, sum 6, etc., and then top 5 from all current/possible lines
    (See attachment for further details)

    Originally I figured out formulas to do the first part (a sum offset and index row, respectively), but not the adding in of the sorting. Trying to figure out if there's a way at all to have a singular formula that would do what I'm looking for it to, plus be responsive to any future data we put in so as not to remember to have to re-calculate at times needed. If what I'm looking for isn't possible, no worries! In that case I'll make a note to whoever enters figures then to be sure to do the sorting portion at certain intervals throughout the year. Thanks!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!