Excel - sum largest 5 numbers out of 6

Copper Contributor

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:



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


Many thanks


4 Replies
best response confirmed by Hans Vogelaar (MVP)








for example:



That's brilliant - thank you so much - you have saved me hours!


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),