SOLVED

# Excel - sum largest 5 numbers out of 6

Copper Contributor

# 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

4 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Excel - sum largest 5 numbers out of 6

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

or

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

# Re: Excel - sum largest 5 numbers out of 6

for example:

=SUM(LARGE(IF(MOD(ROW(D7:D19),2),D7:D19),SEQUENCE(5)))

# Re: Excel - sum largest 5 numbers out of 6

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

# Re: Excel - sum largest 5 numbers out of 6

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

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Excel - sum largest 5 numbers out of 6

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

or

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