Method 9 and 10: Going SUM fancy!
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
=SUMPRODUCT(LARGE(numbers*-(salesperson=”Ben”),ROW(A1:A10)))
So what are we doing here. Remember to understand the formula, always look at it inside-out and 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
=SUM(LARGE((numbers)*(salesperson=”Ben”), ROW(INDIRECT(“1:10″))))
=SUM(LARGE((numbers)*(salesperson=”Ben”), ROW(a1:a10)))
Method 11: Rivet the Pivot for the 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 way to pull this off!
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