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.

Function_Num

1 - 11 - will include rows hidden by Manual Formatting and Grouping.
101 - 111 - will exclude rows hidden by Manual Formatting and Grouping.
Hidden rows which are the result of Filtering are always excluded.

Hidden Rows - By Filtering

Rows can be hidden 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".
1 - 11, will exclude rows hidden by filtering. The subtotal function has not changed (row 10).
101 - 111, will exclude rows hidden by filtering. 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.
1 - 11, will include manually hidden rows. The subtotal function has not changed (row 9).
101 - 111, will exclude manually hidden rows. 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.
1 - 11, will include rows hidden by grouping. The subtotal function has not changed (row 10).
101 - 111, will exclude rows hidden by grouping. The subtotal function has changed (row 11).

Hidden Rows - Bug

If you use a combination of Filtering and Manual Formatting on the same worksheet you may see some strange behaviour.
Lets start with a worksheet that only contains manually hidden rows. Row 5 has been manually hidden.
Row 9 includes the hidden row. Row 10 excludes the hidden row. This is correct.

However if Filtering is applied (and used) on another part of the worksheet, the value in Row 9 will change.
Add a small table in rows 12, 13 and 14 and apply a filter.
Select cell "E9", press F2 (not F9) and you will see a different value returned.
For some reason this function is now excluding manually hidden rows.

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.