Leading the way in Microsoft Office Development

SUBTOTAL(function_num, ref1, ref2, [,ref3] [,…])

 -

SUBTOTAL worksheet function

Returns the subtotal of values in a list or database columns.

Arguments from 1 to 11 ignore hidden rows by AutoFilter and Advanced Filter.

Arguments from 101 to 111 (in Excel 2003) ignore hidden rows by AutoFilter, Advanced Filter and Manually hidden rows.

All other functions carry out their operations on all the rows, regardless of whether they are hidden.

Ignoring Filtered Rows

This function ignores rows that have been hidden as a result of using either the AutoFilter or the Advanced Filter.

Make sure that your calculation is set to Automatic.

The total provided by the SUBTOTAL function in cell "E19" returns the total of all the Amounts.

When you filter the table using AutoFilter the subtotal will change automatically to reflect only the visible rows.

Ignoring Manually Hidden Rows

When you use the arguments 1 to 11 this function will still include any rows hidden using (Format > Row > Hide).

In Excel 2003 it is possible to create subtotal calculations that ignore all hidden rows.

To ignore all hidden rows, regardless of how they were hidden either by filtering or manually you need to use the arguments 101 to 111.

Lets change the function argument to 109 and repeat the filter above.

If we manually hide row 7 the subtotal function will update to reflect that this row is now being ignored.

Applying Subtotals Automatically

This can be done using (Data > Subtotals). For more details refer to the Automatic Subtotals page.

Things to Remember

The argument 3 (which represents the COUNTA function) returns the total number of visible rows.