Home/Excel tips/10+ ways to SUM Top N values in Excel data range – Ready to be Ben 10 of Excel?
10+ ways to SUM Top N values in Excel data range – Ready to be Ben 10 of Excel?
10+ ways to SUM Top N values in Excel data range – Ready to be Ben 10 of Excel?
We all love to add things up. Its always good if things add up to the way we want. And no doubt from Excel newbie to pro everyone uses SUM formula. We have recently discussed how to do conditional SUM as well where we learnt to add up only those values that certain criteria.
However, we may wish to add up only the top 2 or 3 or 10 or any N values while doing calculations on simple data range to complex data using pivot tables. Following methods help you understand how different functions / formula in Excel help you get the total of top or first ‘N’ values in data set.
To best understand and apply the methods learnt in this tutorial please download this excel workbook and follow the instructions below to master each technique.
Again this is a fairly large post and with animations it grew even more. So it has been divided in four pages. Don’t forget to check out other methods on next pages.
Just to clear out confusion if there is any, the following methods are based on simple numbers but you can use the calculation on Dates as well with a little to no additional effort. So try out the methods on dates as well and tweak up the formula a little if needed.
Method 1: Use Excel LARGE function
LARGE function let you find the nth largest value in the data set. Meaning it can find you the largest, second largest, third largest or 10th largest or whatever largest value you want, it is very easy.
Syntax of LARGE function is:
=LARGE(array,k)
Array is collection of item out of which you are trying to find out the nth largest value. This can be a simple data range or a named range.
Where K acts as a point of reference and is said as kth largest or nth largest value to be found. For example in a collection of three items 10, 11 and 12 the largest is 12 for which K=1. For second largest i.e. K=2 the value is 11.
Knowing this, if you want to know the sum of the top 3 values in a data range you can do so using following steps:
Step 1: Open the file and go to tab named:
Step 2: Observe the given information, your data is in range. In cell put the following formula and press Enter key
=large(range,1)+large(range,2)+large(range,3)
Bonus Tip – Using named ranges with LARGE function
If your data is large then selecting the range every is a peril in the way of efficiency so select the range once and go to name box and type any name you want to give to data range. In our case our range is Sales column and we named it: numbers.
The other column of is Sales person. Select the data of that column as well and name it: salesperson. We are going to need that later.
This way formula writing will be much easier and saves you selection time and errors in selection. With name defined your formula will become:
Method 2: Using duo of SUM and LARGE function with Arrays
Well its quite manageable if you are summing up the top three values but what if you want to have a sum of top five values? You will have to write the formula whole five times. Now this is seriously going to enLARGE! So cut the crazy bit out by changing the formula to this:
=SUM(LARGE(numbers,{1,2,3}))
The above is an array formula we have mentioned how many top values to be summed in an array fashion (look closely we have curly brackets). That part of the formula i.e. LARGE function, finds the top three values from the data set. To confirm select the LARGE(numbers,{1,2,3}) part of the formula and press F9 key and you will get:
{299,298,297}
Press Ctrl+Z to have the LARGE function back in place and press Enter
Method 3: Using SUM, LARGE and ROW function 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): Using SUMIF function to sum top n values
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 🙂
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:
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: 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 6: Using Sort and Filter to SUM top n values
Let’s move our focus from pure formula based approach to get top n values summed in Excel. Let’s talk about other features we can use to get this thing done so that you can breathe a little 🙂
There are easier ways to do this as well. Well one of the ways, irrespective of its practicality is that you sort the data in largest to smallest fashion and select the first n number of values and add them up. You can also get the SUM in the status bar on the fly. But this is probably the least possible way to do it. And that is why I am not shy of saying it a total noob way. But it doesn’t matter if it gets thing done.
A basic problem with the Sort feature is that it moves the whole row. And the change is permanent and the only way to get back in previous shape of data is to UNDO the sort. That is the reason it might be out of question.
A better way to get it done is filter and yes it is possible but also practical and excellent 🙂
Method 7: Use SUBTOTAL on filtered or visible results
What if we can filter the top 3, or 5 or 10 or any N number of results and get them summed up? Well yes it is a great idea. Have the data filtered and get the top n values you want and have them summed.
But the catch is normal SUM function don’t work as it does not obey filter. It will still SUM all the values irrespective if filter is applied or not. However we do have another karate kid for this 🙂 the SUB-ZERO oooh I mean… SUBTOTAL…. went too far in computer games… my bad…
Syntax of SUBTOTAL function is:
=SUBTOTAL(function_num,ref1)
Function_num: is the identifier number of function that you want to apply on the data range. It can be SUM, AVERAGE etc. There is a whole list of it.
ref1: data range on which you want to apply the function.
In our case function is SUM for which number is 9 and data range is already named as: numbers.
But before you apply the formula let’s apply the filter. As an example we are filtering for the Top 10 values.
Step 1: Have an active cell within range and go to Data tab and in Sort and Filter group click Filter button. This will activate filter controls on the range.
Step 2: Scroll to the header. Click the filter icon just on the right of heading and from the menu hover mouse to “Number filters” and from the sub menu select Top 10. Data will get filtered for top 10 values in the data range.
Step 3: Just under the last value in the range put the following formula and hit enter:
=SUBTOTAL(9,numbers)
Voila! it will sum just the filtered values leaving or ignoring the values that are hidden or filtered out.
Applause! You have just excelled at filtering data by learning one awesome thing about it!
Method 8: Using Tables to get SUM of top “N” values
If we recall from our 20+ Excel Table tricks article, we know that Excel tables come with built in filter feature and with tables lot of things are easier. To get the SUM of top N values using table follow these steps:
Step 1: Have an active cell within range and hit Ctrl+T (T = Table). Make sure “your data has header” is checked and hit OK button. You have the range converted in Table.
Step 2: Go to the header of the column containing the numbers out of which you want the sum of n values and filter it the same we did in our previous approach i.e. click filter icon and from number filters select Top 10.
Step 3: Having the data already filtered go to Table’s contextual tab and in Table Style Options group check-on the Total row feature. This will get you the total row below the range.
Step 4: If the subtotal is not already appearing the click active the cell inside total of the same column and select SUM from the list using drop down. And there you have it. Saving you even writing SUBTOTAL formula yourself and getting you additional benefits of Tables. Awesome!
Following are some more formula to have the job done for you. Remember numbers is a name of range we are targeting.
What IF we are just SUM simple?
=sum(if(rank(numbers,numbers)<11,numbers,0))
Enter this formula as an array formula using Ctrl+Shift+Enter to make it work properly.
The next of kin to SUBTOTAL
=SUM(AGGREGATE(14,1,numbers,ROW(A1:A2)))
This formula’s entry and structure is almost the same as subtotal. Aggregate formula let you apply almost the same number of functions as SUBTOTAL but with more flexibility regarding hidden, filtered data etc. Watch the following animation to learn how this formula is punched in:
>animation
Special Tip – SUM Top N values – Multiple conditions
Until now we were circling around the single condition i.e. Top N values. But what if we additional condition(s) as well? For example we want to sum the top 10 sales made by Mr. Ben? In here now we have to include Mr. Ben contingency as well. Excel is at rescue for us here as well.
But let’s first understand how to design a formula. We want to sum top 10 sales made by Mr. Ben. Therefore:
First we have to get the Mr. Ben’s sales
Then we will find the top 10 sales values
Then we will add all ten of them
Ben’s Ten! in 1-2-3! Use either of the following formula to have it done:
For example the sales person’s range is named as: salesperson
So what are we doing here. Remember to understand the formula, always look at it inside-outand it will be easier for you.
(salesperson=”Ben”) part looks for entries with Ben only in them and ignores he rest.
–( the two dashes convert the logical array i.e. True and False to 1 and 0 to get the product.
(numbers*–(salesperson=”Ben”) combining the numbers range and salesperson range already worked up for only Ben, this part picks only those numbers or sales that are made by Ben and ignores the other as for others value is False or 0.
ROW(A1:A10) helps us get the array of 1 to 10 in the form of: {1;2;3;4;5;6;7;8;9;10}
LARGE formula operates on the array mentioned inside it considering the numbers resulted from ROW formula.
Finally SUMPRODUCT Sums them all.
You can also use the following array formula to get the sum of top 10 sales made by Ben. As these are array formula you have to enter them using Ctrl+Shift+Enter combo
Method 11: Using Pivot table to SUM of Top N values
Once you have the pivot table you can filter the data to get the sum for Top 10 numbers or results for a specific component of data. This works the same way as if you are doing with table. Click Filter button and this time from value filters select Top 10. A dialogue box appears from which you can specify for what Top N values you want the SUM done.
Bonus tip – A SMALL one
After learning all about TOP N values, fancying about summing up the lowest N values from the data? Just replace LARGE with SMALL and I think almost all of the formula above will work. I haven’t checked the above formula for SMALL formula but I am very much hopeful it will come out great 🙂
So here you have 10+ ways to get the Top N values getting summed up using formula, features in Excel and everything in between.
What approach do you use to get this done? Please share your tricks and ways. I would love to know additional ways to pull this off!
Hi, I'm Mr Z-Lib Admin of Z-Lib.click .Thank you so much and I hope you will have an interesting time with Z-Lib.click . Mail: webappninja1@gmail.com - PHONE : +84779313987. Learn More →
10+ ways to SUM Top N values in Excel data range – Ready to be Ben 10 of Excel?
Hi, I'm Mr Z-Lib Admin of Z-Lib.click .Thank you so much and I hope you will have an interesting time with Z-Lib.click . Mail: webappninja1@gmail.com - PHONE : +84779313987. 
Leave a Comment