Totaling the Nth Largest Values In Excel

Totaling the Nth Largest Values In Excel

If you ever need to find the total of the 5 largest values in a column, there are several options available to you.

The most obvious solution is to select the 5 largest values and read the SUM in the Status Bar. You could also sort your values, select the largest 5 and read the SUM in the Status Bar.

AutoFilter allows you to filter the ‘Top n Items’ which makes it easy to calculate their SUM.

If you are just doing this once or very rarely, either of these options may be OK. However, sometimes you do not want to rearrange or filter your data, so these options would not be practical.

Other times the values you are evaluating may change regularly, so doing this manually may not be the best option.

Wouldn’t it be nice to have a formula that could calculate this total for you automatically, even as your data changes? Again, as with most things in Excel, there are multiple solutions available.

One of Excel’s built-in functions that can help us with this task is LARGE.

Syntax:  LARGE(data_set,n)

The LARGE function returns the nth largest value in a data set. The data set can be a range of cells or an array of values. For example, the 5th largest value in a range.

=LARGE(A1:A10,5) would return the 5th largest value in the range A1 to A10.

Knowing this, we can build a simple formula to sum the top 5 values in this range.

(i.e. 1st largest plus 2nd largest plus 3rd largest plus 4th largest plus 5th largest)

This will give you the answer you are looking for but is there a better way?

Yes. Another option to perform this task, which most Excel users are unaware of, is an array formula.

Rather than enter multiple functions to get result we want, we can use the following array formula to quickly do it for us in one formula. In this example we want to SUM the 5 largest values in A1 to A10.

In most cases when you use ‘array formulas‘ in Excel, you must press CTRL+SHIFT+ENTER (rather than just ENTER) after you have typed the formula.

However, some functions will accept arrays without requiring you to use CTRL+SHIFT+ENTER. The LARGE function is one of those functions.

In this example, we can enter the following array formula to sum the 5 largest values in cells A1:A10.


Notice that we have inserted an array of numbers {1,2,3,4,5} as the second argument of the LARGE function. The surrounding ‘curly braces’ tell Excel that this is an array and to SUM the results as if there were a LARGE function in 5 separate columns.

You can use this same technique to find the SUM of the SMALLest values or the AVERAGE of these values.

Use an array formula to find sum of largest values in Excel 2007-2010

An alternative to typing each value into an array is to use ROW(1:5) where 1 represents the lower limit and 5 represents the upper limit of your array. Consider a situation where you want the top 100. You wouldn’t want to type all 100 numbers into the array. Note that, in this case, you will have to press CTRL+SHIFT+ENTER (rather than just ENTER) after you have typed the formula and after each time you edit the formula to create a true array formula.

Leave a Reply

Your email address will not be published. Required fields are marked *