 |
 |
| |
|
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 seesum 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 sumboth the 5 largest and smallest values of A2:A100. Themost efficientformula method is viaDSUM.
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 viaan 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 todelete rows meeting a criteria. Here I will show you how to useAdvancedFilter (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 WorksheetDim 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 = TrueEnd 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