Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Formulas: How To Sum The Top Or Bottom X Numbers in a List

| | Information Helpful? Why Not Donate.

 

Originally Posted in our Excel Help Forum

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 CriteriaSum Every Nth Cell | Excel Subtotals |Making the SUBTOTAL Function Dynamic |Bold Excel Subtotals Automatically |Count of Each Item in a List | Excel Pivot Tables

How To Sum The Top Or Bottom X Numbers

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

=SUMPRODUCT(($A$2:$A$100>LARGE($A$2:$A$100,9))*($A$2:$A$100))

Your list cannot contain text

DSUM THE BEST WAY

Use of the DSUM Function requires A1 to be a Heading.

  1. In any spare column add the text Criteria
  2. Directly below this Enter either =A1>LARGE($A$1:$A$100,9) OR =A1<SMAll($A$1:$A$100,9)
  3. 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 CriteriaSum 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 Add-ins 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

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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 & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates