 ALL YOUR EXCEL NEEDS |
 |
| FREE Excel STUFF |
| Excel Newsletter |
Advanced Search  |
| PRODUCTS |
| Development |
| Contact Us |
Back to Excel Newsletter Archives
|
Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee |
| Excel Help |
Free
Training |
Free Video Tutorials | All
Free StuffAlso see 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
There are at least 2 ways to sum only the nth largest or smallest cells values in a column. Here they are in order of efficiency.
However, before using ANY formulae, consider a PivotTable showing and summing only the top or bottom x values. This is by far the MOST efficient.
Lets say we have a heading in A1 and numbers in cells directly below the heading from A2:A100 and we wish to sum both the 5 largest and smallest values of A2:A100. The most efficient formula method is via DSUM.
SUM 5 LARGEST
In C1 put the heading Criteria. If using 2007 use: =$A$1
In C2 Enter: =">="&LARGE($A$1:$A$100,5)
In any cell Enter: =DSUM($A$1:$A$100,$A$1,$C$1:$C$2)
SUM 5 SMALLEST
In C1 put the heading Criteria.
In C2 Enter: ="<="&SMALL($A$1:$A$100,5)
In any cell Enter: =DSUM($A$1:$A$100,$A$1,$C$1:$C$2)
SUMPRODUCT
=SUMPRODUCT(($A$2:$A$100>LARGE($A$2:$A$100,9))*($A$2:$A$100))
Your list cannot contain text
Again we have a heading in A1 and numbers in cells directly below the heading from A2:A100 and we wish to sum both the 5 largest and smallest values of A2:A100. The least efficient formula method is via an Array Formula
SUM 5 LARGEST
=SUM(LARGE($A$1:$A$100,{1,2,3,4,5}))
This is an array formula and must be entered via Ctrl+Shift+Enter and your list cannot contain text
SUM 5 SMALLEST
=SUM(SMALL($A$1:$A$100,{1,2,3,4,5}))
This is an array formula and must be entered via Ctrl+Shift+Enter and your list cannot contain text
| Excel Help |
Free
Training |
Free Video Tutorials | All
Free StuffOne of the most common requests is to delete rows meeting a criteria. Here I will show you how to use AdvancedFilter (VBA Uses) to leave ONLY the rows meeting your criteria. For this example, we will use a date criteria. That is, all rows where the date column is less than the current date. Use DateSerial(year, month, day) for non current date or the DateAdd(interval, number, date). See Excel VBA help for details
IMPORTANT: Deleting rows can cause #REF! errors throughout your Workbook within Formulae, Charts, Named Ranges etc. The advantages to using AdvancedFilter are;
Doesn't delete rows
Very fast
Sub KillRows()
Dim wsTemp As Worksheet
Dim wsTable As Worksheet
''EXCEL 97 OR ABOVE
''WRITTEN BY WWW.OZGRID.COM
Application.ScreenUpdating = False
Set wsTable = ActiveSheet
Set wsTemp = Sheets.Add
With wsTemp
'Change "A1" ONLY to reference your heading
.Cells(1, 1) = "='" & wsTable.Name & "'!A1"
'Use DateSerial(year, month, day) for non current date or the DateAdd(interval, number, date)
.Cells(2, 1) = "<" & Date
'Name both criteria cells
.Range("A1:A2").Name = "AFcriteria"
End With
'AdvancedFilter Code
'http://www.ozgrid.com/VBA/advancefilter.htm
With wsTable.UsedRange
.AdvancedFilter xlFilterCopy, Range("AFcriteria"), wsTemp.Cells(1, 5)
'CLEAR NOT DELETE
.Clear
'Copy back the filtered results
wsTemp.Cells(1, 5).CurrentRegion.Copy Destination:=.Cells(1, 1)
End With
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
| Excel Help |
Free
Training |
Free Video Tutorials | All
Free StuffInstant 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