Method 6: Breather - Using Sort 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 not possible but also practical and excellent 🙂
Method 7: Breather - Subtotal 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!
Don’t forget to checkout:
Introduction to Excel Tables – Data beyond just formatting
20+ Excel Table tricks to turbo charge your data
Method 3 + Method 4 + Method 5 | Method 9 + Method 10 + Method 11
Good day
i have a challenge . been working on a report card, but i can’t go through 🙁
NAME ENG MATHS ADMA BIO GEO CIV PHY CHE AVG PSN
BRIAN 55 3 66 2 63 2 72 2 55 3 71 2 83 1 80 1 427 68 8
BKALUMO 65 2 52 3 0 FAIL 65 2 70 2 60 2 88 1 50 3 400 56 16
IAN 70 2 24 FAIL 36 FAIL 50 3 58 3 71 2 75 1 18 FAIL 360 50 22
TREVIS 88 1 54 3 51 3 80 1 70 2 77 1 90 1 72 2 477 73 3
GRIFFINS 68 2 26 FAIL 51 3 46 4 59 3 77 1 70 2 60 2 385 57 1 HARRISON58 3 12 FAIL 34 FAIL 54 3 55 3 71 2 75 1 40 4 353 50 25
ROSTEN 78 1 42 4 45 4 58 3 74 2 63 2 85 1 80 1 438 66 7
VICTOR 78 1 56 3 60 2 56 3 59 3 83 1 85 1 24 FAIL 421 63 9
GABRIEL 48 4 38 FAIL 33 FAIL 52 3 40 4 49 4 75 1 12 FAIL 302 43 28
The card works like this . a teacher will enter the mark for a pupil then it will calculate the grade ie 1-9(fail). and also calculates the best 6 subjects including english as shown in the 3rd last column.(SUM(LARGE(E3:Q3,{1,2,3,4,5}))+C3)
i want to now add a column that will calculate the points (sum of best grade). i have tried alot of formulars but the challenges comes because the grades where found by a formular and are not in a range (in different columns)
=SUM(SMALL(range,{1,2,3,4,5}))+M34 - this formular isnt giving an answer but an error ### #num!. am stuck on how to go about it. i noticed it could be because the grades for a particular pupil are in different columns and are a result of a formular.
Please help. Thanks in advance
Hi Fazal,
Good day…
A very nice article, thanks for sharing.
“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”
I would like to add tgat excel help says:
Criteria is the condition that must be met if a value is to be included in the sum. If the condition contains any text, logical or mathematical symbols it must be enclosed in double quotation marks. Quotation marks are not necessary if the criterion is just a number.
Regards,
Khalid
what to do if i want to get the sum of large “N” numbers from multiple worksheets.
Thanks in advance
Hi Hasaan,
I have a question about how to take the next step in conditional summarising , I would like to summarise volume by highest Profit not by highest volume, to arrive at the last column here. The column was done manually.
I’m trying to make sales fall into the profitability categories: 50 best transactions, next 25 best transactions etc.
I will use three if statements to achieve the category name, but the summation of volumes based in highest profit eludes me. Do you have an idea? I can’t sort the data set because I have 4 quarters to do.
The data set is very large, but this gives a good indication:
Profit Vol Rank Cumulative sales by highest profit
What Q1 Q1 Q1 Q1
Goods 1 350000 53 1 0-50
Goods 1 40000 7 4 75-100
Goods 1 100000 17 2 50-75
Goods 1 20000 3 8 75-100
Goods 1 70000 5 3 50-75
Goods 1 26000 4 5 75-100
Goods 1 -10000 19 11 75-100
Goods 1 20000 3 7 75-100
Goods 1 22000 5 6 75-100
Goods 1 5500 1 9 75-100
Goods 1 -500 0 10 75-100
Hi,
I have a similar issue that I cant find a good solution too without creating new columns of data. I have two columns, A and B. I want to find the average of the top three values in column B given that the condition X in column A is met at the same time. I have tried all kinds of formulas but I can’t seem to get it right.
Your help is very much appreciated!
Hello, I wanted to do a Conditional TOP N Items Sum… I have a Column A where i have City Name (27 Cities), Column B Where an Establishment Name, Column C where Order Qty. Its a huge list of 32000 Establishment. Now i want to know the Order Qty of Each of the 27 City from the top 10 Ordering Establishments. How Can i do that. I have gone through this but could not make use of it.
Hey Ruchir - I had a similar problem, one workaround is to first use Sort the dataset by col A and then use Find & Replace (Ctrl+H).
After sorting, add a Subtotal to your dataset through the Data tab on the Ribbon - could be anything, but let’s use COUNT for the example. At every change in City Name, put a count on Col C, i.e. Order Quantity.
Now, find and replace all the = signs on the sheet with a blank. I will essentially use find and replace to alter the formula to give us the the results, so removing the blank prevents any errors through the process - we’ll bring it back in the end 🙂
Now find and replace all the ) signs with ,{1,2,3,4,5,6,7,8,9,10})). After this, find and replace all the SUBTOTAL(3, with =SUM(LARGE(
I think that should solve it 🙂
Cheers,
Vasudha
*is to first sort the dataset