Sum the largest numbers in Excel

This example demonstrates how to create an array formula that sums the largest numbers in a range.

1. We use the function LARGE (LARGEST). To find, for example, the second largest number, you need to enter the following formula:

   =LARGE(A1:A11,2)

   =НАИБОЛЬШИЙ(A1:A11;2)

2. To sum the 4 largest numbers, add the function SUM (SUM) and replace 2 with {1;2;3;4}.

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

   =СУММ(НАИБОЛЬШИЙ(A1:A11;{1;2;3;4}))

   

3. Let’s finish with a click Ctrl + Shift + Enter.

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

   =СУММ(НАИБОЛЬШИЙ(A1:A11;{1;2;3;4}))

   

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.

Explanation:

• Range (array of constants) created with a function LARGE (LARGE) is stored in Excel memory, not in worksheet cells.
• The array of constants looks like this: {22;10;8;6}.
• This array of constants is used as an argument to the function SUM (SUM), giving a result of 46.