Forum Discussion

Simon0307's avatar
Simon0307
Copper Contributor
Sep 25, 2023
Solved

Excel - sum largest 5 numbers out of 6

I need to add the 5 largest numbers together from a list of 6. The 6 are not in a consecutive line and there are other numbers in between. The list are on the following rows:

D7,D9,D11,D13,D15,D17

 

Can someone assist in giving me a formula that gives me the sum of the largest 5 from these 6

 

Many thanks

 

Formulas and Functions

4 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 25, 2023

    Simon0307 

    Since the numbers alternate within a list, WRAPROWS will move the unwanted values to a fresh column.  SORT can be set to sort descending by the first column.  TAKE selects the first five rows of the first column.

    = LET(
    filtered, WRAPROWS(list,2),
    topfive,  TAKE(SORT(filtered,,-1), 5, 1),
    SUM(topfive)
  )

  • JosWoolley's avatar
    JosWoolley
    Iron Contributor
    Sep 25, 2023

    Simon0307 

    =SUM(LARGE((D7,D9,D11,D13,D15,D17),{1;2;3;4;5}))

    or

    =LET(φ,(D7,D9,D11,D13,D15,D17),SUM(φ)-MIN(φ))

     

     

    • Simon0307's avatar
      Simon0307
      Copper Contributor
      Sep 25, 2023
      That's brilliant - thank you so much - you have saved me hours!

Resources