
Current Special! Complete Excel
Excel
Training Course
for Excel 97  Excel 2003, only $145.00.
$59.95 Instant
Buy/Download,
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
Also see: DSUM  sum Function Sum With Multiple Criteria  Sum Every Nth Cell  Excel Subtotals Making the SUBTOTAL Function Dynamic Bold Excel Subtotals Automatically Count of Each Item in a List  Excel Pivot Tables
When you have a list of number in an Excel spreadsheet there are times when you may have to Sum only the top x numbers in the list. If the numbers are sorted then the task is a fairly straightforward Sum function including on the top, or bottom x cells. Often however this is not the case.
Lets say we have a list of number in the range A1:A100 and we have to sum only the 10 largest/smallest figures. We can do this by nesting the LARGE function into the SUM function as shown below. CAUTION: Array formulas and SUMPRODUCT (used for this purpose) are VERY inefficient and have serious limitations. I advise to use DSUM (shown below) or a PivotTable
=SUM(LARGE($A$2:$A$100,{1,2,3,4,5,6,7,8,9,10}))
This is an array formula and must be entered via Ctrl+Shift+Enter and your list cannot contain text
If we only needed the 5 largest, we would simply omit the numbers 6 to 10. If we needed to sum the 15 largest number we simply add number 11 to 15.
Should we need to sum the 10 smallest values in the list we could use
=SUM(SMALL($A$2:$A$100,{1,2,3,4,5,6,7,8,9,10}))
This is an array formula and must be entered via Ctrl+Shift+Enter and your list cannot contain text
=SUMPRODUCT(($A$2:$A$100>LARGE($A$2:$A$100,9))*($A$2:$A$100))
Your list cannot contain text
Use of the DSUM Function requires A1 to be a Heading.
 In any spare column add the text Criteria
 Directly below this Enter either =A1>LARGE($A$1:$A$100,9) OR =A1<SMAll($A$1:$A$100,9)
 Where you want the result, Enter =DSUM($A$1:$A$100,1,$C$1:$C$2)
Where C1=Criteria and C2=A1>LARGE($A$1:$A$100,9) OR =A1<SMAll($A$1:$A$100,9)
Where 1 is the relative Column position of the Column you are summing. Your MUST have a heading but your list CAN contain text.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Also see: DSUM  sum Function Sum With Multiple Criteria  Sum Every Nth Cell  Excel Subtotals Making the SUBTOTAL Function Dynamic Bold Excel Subtotals Automatically Count of Each Item in a List  Excel Pivot Tables
Special! Free Choice of Complete Excel Training Course OR Excel Addins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages  Trading In Excel  Construction Estimators  Finance Templates & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates