24 November, 2021

Summing up a variable number of the Largest or Smallest values in a range

Here is a range of numbers provided as an example. It could be any unsorted range/list of numbers you're working on:

Your data set is large and for some specific analytical reason you'd like to track the sum of several largest and smallest numbers in your set. The ARRAY formulas come to help and are very efficient in such cases.

Based on the range of data presented above, the following table is an example of output you can obtain with array formulas:

The following formulas have been used in cells Q3 and Q4 to get the sum of five largest and smallest values; both formulas reference cell Q2 (where number 5 was entered into the cell):

=SUM(LARGE(\$A\$2:\$J\$6,ROW(INDIRECT("1:"&Q\$2))))
=SUM(SMALL(\$A\$2:\$J\$6,ROW(INDIRECT("1:"&Q\$2))))

To obtain sums for any other number of the largest and smallest values you just enter the numbers, like 1 to 10 in the row 2 in this example, and refer to them (cells M2 to V2) in the formulas copied to the remaining columns in row 2 and 3.

Remember that these are ARRAY formulas, so - after typing them in - they must be entered using CTRL+SHIFT+ENTER keyboard sequence.

In reference to cell M2 (=1) you get, obviously, the Maximum and Minimum value from your data set.

Further, the sums of the largest and smallest values can be presented graphically, if needed, like in these examples (Funnel chart and Clustered column chart):