SUBTOTAL Function

The SUBTOTAL function returns the subtotal of values in a list, table or cell range.
In Excel 2010 this function was replaced with the AGGREGATE function.
In Excel 2007 this function was modified to always exclude hidden rows.
This is a very useful function and can be used to perform a large number of different calculations.
One of the most common uses for this function is (or was) to provide totals (or sums).

Hidden Rows - By Filtering

Hidden rows which are a result of using AutoFilter are excluded.
Rows can be filtered by applying an AutoFilter (Data, Sort and Filter > Filter)
Lets filter the table using the "Category" column to display only the rows which have "Food" in column "B".

The subtotal will change automatically to reflect only the visible rows (assuming your calculation is set to automatic).
Notice the total in cell "E19" has changed.

Hidden Rows - Manually Hiding

Hidden rows which are a result of Manual Formatting are excluded.
Rows can be manually hidden using (Home, Format > Hide & Unhide > Hide Rows).
If we manually hide row 7 the subtotal function will update again to reflect that this row has been hidden.

Hidden Rows - By Grouping

Hidden rows which are a result of Grouping are excluded.
Rows can be hidden as a result of a grouping (or outlining) using (Data tab, Outline > Group).
SS

Nested SUBTOTALs

For more details refer to the Nested Subtotals

Automatic SUBTOTALS

In fact there is a button on the Data tab dedicated to this function and its lets you add Subtotals

Important

The SUBTOTAL function and the AGGREGATE function are the only functions that can ignore rows that are hidden as a result of filtering.
The argument 3 (which represents the COUNTA function) returns the total number of visible rows.
In Excel 2003, if you use arguments 1-11, any rows hidden by Grouping were not excluded. This was fixed in Excel 2007.
In Excel 2003, if you use arguments 1-11, any rows hidden by Nested Functions were not excluded. This was fixed in Excel 2007.