SUBTOTAL Function

The SUBTOTAL function returns the subtotal of values in a list, table or cell range.
This is a very useful function and can be used to perform a large number of different calculations.

Hidden Rows - By Filtering

Rows can be hidden by applying an AutoFilter (Data, Sort and Filter > Filter)
Hidden rows which are the result of Filtering are always excluded.

Lets filter the table using the "Category" column to display only the rows which have "Food" in column "B".
With the numbers 1 - 11, rows hidden by filtering are excluded. The subtotal function has not changed (row 10).
With the numbers 101 - 111, rows hidden by filtering are excluded. The subtotal function has changed (row 11).

The subtotal function will change automatically assuming your calculation is set to automatic.

Hidden Rows - Manual Formatting

Rows can be Manually Hidden using (Home, Format > Hide & Unhide > Hide Rows).

What happens when we manually hide row 5.
With the numbers 1 - 11, manually hidden rows are still included. The subtotal function has not changed (row 9).
With the numbers 101 - 111, manually hidden rows are excluded. The subtotal function has changed (row 10).

Hidden Rows - By Grouping

Rows can be hidden as a result of a Grouping (Data tab, Outline > Group).

What happens when we collapse the Food group.
With the numbers 1 - 11, rows hidden by grouping are still included. The subtotal function has not changed (row 10).
With the numbers 101 - 111, rows hidden by grouping are excluded. The subtotal function has changed (row 11).

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

Used with Tables

When you create tables and include a Total Row the SUBTOTAL function with 109 is used.

=SUBTOTAL(109,[column-name]) 

Updates

The argument 3 (which represents the COUNTA function) returns the total number of visible rows.
In Excel 2007 this function was modified to always exclude hidden rows. It cannot exclude hidden columns.
In Excel 2007, if you use arguments 1-11, any rows hidden by Grouping are excluded.
In Excel 2007, if you use arguments 1-11, any rows hidden by Nested Functions are excluded.