Thursday, June 01, 2006
Using the SUBTOTAL Function in Excel
The Subtotal function is commonly used when creating Subtotals on a table with the Data | Subtotals Menu command. However it can be just as easily used by writing the formula and has some powerful uses.
The syntax is SUBTOTAL(function_num,ref1,ref2,...)
The function numbers allow you to use any of the following functions in the subtotal:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
The most common is of course the SUM function.
When and why should you use SUBTOTAL and not just the SUM function on its own.
- The SUBTOTAL function has some powerful abilities that the commonly used SUM function doesn't. For example when summing a list of cells that contains hidden rows or columns the SUM function will include them. However the SUBTOTAL function only operates on visible cells. The values of the hidden cells are excluded from the total. This is especially useful when filtering a list if you want to see the total of the visible (or filtered) cells.
Note by default, if you have a filtered list of values and you double click the sum (sigma) icon on the toolbar after you select a cell below the bottom of the filtered range Excel will not use a SUM formula but automatically use a SUBTOTAL function. - The SUBTOTAL function ignores other subtotals within the range. So if you have several subtotals and a grand total the grand total formula can be the subtotal of the entire range that includes the subtotals and it will ignore them.
The Excel Guru's Free Tips and Hints.
Welcome
Welcome to the Excel Tips blog. Free Microsoft Excel Tips and Hints designed to increase your productivity.
Advertisement
Subscribe Me
Previous Posts
- Protecting Cells in Excel
- Gregorian Dates in Excel
- MMULT Formulas in Excel
- VBA tricks - capturing spreadsheet data in VBA
- Introducing Office 2007 + PDF Handbook for Office
- Range Names in Excel
- IF statements and conditional formats in Excel
- Common errors in Excel
- Creating Sheet Tabs in Excel with the Number of th...
- Disabled Items in Excel - AKA crashed files
My Blogs and Websites
- Spy Journal Home
- Spy Journal Archives
Personal Blog- Excel Tips Blog
- Blog Tips Blog
- Tech Tips Blog
- Urban Space Novel
- Photo Archive
- Parklife Results
- Parklife Soccer Club Website
- KROSTech LAN Parties
- Jethro Management
- Jethro Consultants
- Oz Bush Poet
Excel Links
- ---RSS Feeds---
- Unofficial Microsoft Office Stuff
- KC on Exchange and Outlook
- Office Zealot
- Automate Excel
- Colo's Excel Junk room
- Daily Dose Of Excel
- The Planning Deskbook
- Excel Pragma
- Andrew's Excel Tips
- ---No RSS Feed---
- Excel Tip
- Mailbarrow
- Experts Exchange
- AJP Excel Information
- Beyond Technology
- Contextures
- ExcelTip
- Mr Excel
- Pearson Software Consulting
- Peltier Technical Services
- John Walkenbach - J-Walk
- Vangelder (wont work in Firefox)
- VBA Express
- xlDynamic
- The Excel Nexus
- Code Net
- Better Solutions
- Excel Business Tools
- Data Pig Technologies
- VBA Express
Other Links
Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.
Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.