Spy Journal 3.0 Filtering the web for you

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:

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.

Links to my other Articles about Subtotals including using outlining.

http://www.spyjournal.biz/exceltips/2005/03/creating-subtotals-in-excel.html

http://www.spyjournal.biz/data_filters_in_Excel_2003_and_2007