Excel formula question

Question

How do I return the sum of the three highest numbers in a range?

Answer 1

You should probably mention how you want to handle duplicates.

The "3" highest numbers here are 7,6,5.

The bot solution here uses Large(). OR...do you want the sum of all the top 3 numbers?

ie Do you want 20, 42, or 18 ?

Answer 2

Use the LARGE function with SUM to return the sum of the three highest numbers in a range:

=SUM(LARGE(A1:A20,{1,2,3}))

Replace A1:A20 with your actual range. This returns the largest, second-largest, and third-largest values and adds them together.

If you want a formula that is easier to extend to a different number of highest values, you can use:

=SUM(LARGE(A1:A20,SEQUENCE(3)))

Here, 3 specifies that you want the top three values.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 3

Use:

=SUM(LARGE(A1:A10,{1,2,3}))

This returns the 1st, 2nd, and 3rd largest values in the range, then adds them together.

If the range is different, replace A1:A10 with the actual range.

Example:

=SUM(LARGE(Q16:Q157,{1;2;3}))

LARGE(array, k) returns the k-th largest value in a range, and SUM adds those returned values.

---

References:

• Guidelines and examples of array formulas
• LARGE function