Sum top N values

OfficeTips Home || VBA Section || General Section || Download Section || Privacy Policy

Sum top N values in an unsorted range

The LARGE function of Excel returns the nth-largest value in a given range, in which n is the function's second argument. Let us assume we wish to sum up the Top 5 values in a given range of 50 cells. We need a formula that calls the LARGE function five times and then sums up the results. The following formula, which assumes the numbers are located in the range consisting of 50 cells and have range name TheRange, will do the job:

=LARGE(TheRange,1)+ LARGE(TheRange,2)+ LARGE(TheRange,3)+ LARGE(TheRange,4)+ LARGE(TheRange,5)

If U are familiar with using array formula then here is another solution:

=SUM(LARGE(TheRange,{1,2,3,4,5}))

The formula first passes an array of five values to the LARGE function, and then uses the SUM function to add the values returned by the LARGE function. Please note that the values 1 through 5 are enclosed in brackets rather than parentheses. Since it is an array formula be sure after typing press Ctrl+Shift+Enter instead of Enter.

As n gets larger the formula gets much more bloated . For example, to sum the top 20 values in a range, a formula must contain a list of integers from 1 to 20. A more general version of the array formula:

=SUM(LARGE(TheRange,ROW(INDIRECT ("1:20"))))

Important: The formula given above is an Array Formula. Pressing Enter instead of Ctrl+Shift+Enter will provide a wrong output. This formula uses the ROW function to generate a series of integers between 1 and 20, and uses this array as the second argument for the LARGE function. To sum a different quantity of numbers, just change the 20 to the desired number.