Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com
Learn how to create Excel dashboards.

   Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter October 2008

Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee

Got any Questions? SearchSearch Excel Content | Excel Help |Free Training | Free Video Tutorials | All Free Stuff

EXCEL TIPS AND TRICKS

Sum Only Nth Largest or Smallest Cell Values

Also seesum 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

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

Your list cannot contain text

 

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

Got any Questions? SearchSearch Excel Content | Excel Help |Free Training | Free Video Tutorials | All Free Stuff

EXCEL VBA TIPS & TRICKS

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

Got any Questions? SearchSearch Excel Content | Excel Help |Free Training | Free Video Tutorials | All Free Stuff

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft