OzGrid Excel and VBA Newsletter October 2008

Sum Only Nth Largest or Smallest Cell Values

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.

DSUM

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

1. In C1 put the heading Criteria. If using 2007 use: =\$A\$1

2. In C2 Enter: =">="&LARGE(\$A\$1:\$A\$100,5)

3. In any cell Enter: =DSUM(\$A\$1:\$A\$100,\$A\$1,\$C\$1:\$C\$2)

SUM 5 SMALLEST

1. In C1 put the heading Criteria.

2. In C2 Enter: ="<="&SMALL(\$A\$1:\$A\$100,5)

3. 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))

ARRAY FORMULA

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

1. =SUM(LARGE(\$A\$1:\$A\$100,{1,2,3,4,5}))

2. This is an array formula and must be entered via Ctrl+Shift+Enter and your list cannot contain text

SUM 5 SMALLEST

1. =SUM(SMALL(\$A\$1:\$A\$100,{1,2,3,4,5}))

2. This is an array formula and must be entered via Ctrl+Shift+Enter and your list cannot contain text

delete rows meeting a criteria

One 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;

1. Doesn't delete rows

2. 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`