<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**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 aPivotTable**

**=SUM(LARGE($A$2:$A$100,{1,2,3,4,5,6,7,8,9,10}))**

This is anarray formula and must be entered via **Ctrl**+**Shift**+**Enterand 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 anarray formula and must be entered via **Ctrl**+**Shift**+**Enterand 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=CriteriaandC2=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 Add-ins Collection on all purchases totaling over $64.00. ALLpurchases 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...**