10+ ways to SUM Top N values in data range - Ready to be Ben 10 of Excel?

Method 3: Rowing forward with Old friends to make new Indirectly - Using SUM to sum top n values

After grabbing the basics of LARGE function in no time. Let’s start making our lives a little easier.

Requirements of real world can go in any direction and to any extent. So far we have learnt how to SUM the top three values in a range. But what if we want to sum the top 10 or top 30 values? Well in that case we will have to type all the numbers from 1 to 10 or 30 inside curly brackets and may be by the time you punch in the whole series your fingers are also curled…

So save yourself and let’s make another move. But first let’s understand what we need. We need a simple solution that can give us required series of numbers in an array. There are number of ways to do it but the easiest of all is use to ROW function to generate an array of required N numbers to sum.

Until now we knew ROW function gives us the row number of the cell in which ROW function is applied. For example if we put this formula in cell A11 the result is 11 on pressing Enter.

However, if we put a range to it it transforms into an array. To confirm put following formula in any cell and DO NOT press Enter:

=ROW(a1:a10)

Now select the formula and press F9 key and you will see the following result:

{1;2;3;4;5;6;7;8;9;10}

This is exactly what we are after. So using the last formula we learnt to SUM top three values, in place of curly brackets and its content add in ROW(A1:A3), your formula will look like this:

=SUM(LARGE(numbers,ROW(A1:A3)))

Here is a tricky part, instead of pressing Enter key you have to press Ctrl+Shift+Enter. The reason is that in order to make ROW function to form an array of {1;2;3} you have to command excel to solve it as an array formula. Otherwise formula won’t work. So press the combo and get the result. If you look in the formula bar after hitting the combo of three keys you will find formula to be like this:

{=SUM(LARGE(numbers,ROW(A1:A3)))}

The curly brackets enclosing the whole formula is representing only that this is executed as an array formula and not as a simple formula.

Now if you want to sum the top 10 values simple replace A3 with A10 and you will get it done in which case formula will become:

=SUM(LARGE(numbers,ROW(A1:A10)))

Don’t forget about Ctrl+Shift+Enter!

Another alternative formula to achieve the same using ROW function is:

=SUM(large(numbers,ROW(Indirect(“1:10”))))

The above formula provides you with numbers without making a selection of rows as it was the case in above formula. It is a safer bet.

Method 4(A): Total legacy of SUM extended - SUMIF and SUMPRODUCT

Few days back we learn about advanced and new formula in Excel that provide you ready solution to add up values in data based particular criteria. That way you don’t have to mingle in SUM and IF function together. As we want to sum the top n values, this is basically a condition and we can use SUMIF and SUMPRODUCT to get the job done for us.

Using SUMIF to get the sum of top 10 or any nth number of values the formula can be:

=SUMIF(numbers,”>=”&LARGE(numbers,10))

Remember, “numbers” is the name of range we defined earlier in the discussion. This formula is simply adding up all those numbers that are greater than or equal to the 10th largest number in the range. Simple :). If you want top 3 or 4 or 30 values to be summed then simply replace the figure of 10 in the formula above with your desired amount.

Notice that logical operators are in inverted commas and LARGE function is joined using &. For some reason SUMIF don’t work if we don’t put criteria in inverted commas. If I find the reason I will let you know. And someone out there know the reason please share in the comment section 🙂

To learn how to use SUMIF with dates read: Using SUMIF with Dates in Excel

Bonus Tip - sum top n positive or negative values values

Bonus Tip - SUM the TOP n negative numbers

You might have a data that consists of both positive and negative numbers. If we talk about lowest or largest numbers then positive and negative numbers get counted. However, if you like to sum just top or least n values of positive or negative figures then you can use SUMIF formula very easily:

To sum top 3 positive values in the data range

=SUM(LARGE(IF(E285:E295>0,E285:E295),{1,2,3}))

To sum top 3 negative values in the date you simply have to change > to <

=SUM(LARGE(IF(E285:E295<0,E285:E295),{1,2,3}))

Remember these are array formulas and have to executed by pressing Ctrl+Shift+Enter

A little bloated version of SUMIF to add up top 10 values of positive figures is as follows:

=SUMIF(numbers,”>=”&LARGE((numbers)*-((numbers)>0),10))

Method 4(B) Using SUMPRODUCT to sum top n values

SUMPRODUCT is one fantastic formula that let you do many things so easily and most of the time without even thinking much of structuring the formula correctly. The way it handles the conditions even the multiples of conditions is superb. However, it does have a downside and its use is decreasing since the introduction of SUMIF. The reason is it slows down the workbook because of the way it works. But cutting the details at least for our data and with few hundreds we can use this formula.

=SUMPRODUCT(LARGE(numbers,ROW(A1:A10)))

Now this formula looks surprisingly similar to the SUM formula we applied in method above. Wait for sum with multiple conditions later in this article to see SUMPRODUCT in real action :). So far it is working exactly the same way as simple SUM function was working. I will explain its working in multiple conditions.

To learn how SUMPRODUCT function works and other articles visit this page

Method 5: Another in the rank - Using SUMPRODUCT and RANK to sum top n values

Another variation of of the use of SUMPRODUCT. Here we are using RANK functions. RANK function tells you, well the name is telling the whole story, rank of a number inside a data range. But we made a cheeky use of it. Instead of giving a single number we provided a whole range and put it against a logical test “<11”. As a result this part of the formula churns out TRUEs and FALSEs by checking each number in the range if it is greated than the 11th largest number. By adding two dashes before it we converted a logical array to Boolean array of 1s and 0s as SUMPRODUCT can use 1s and 0s to get the product of numbers or arguments or conditions whatever you call it. So here is the formula you can use:

=SUMPRODUCT(numbers*-(RANK(numbers,numbers)<11))

Method 1 + Method 2 | Method 6 + Method 7 + Method 8