Using the SUBTOTAL() function with the Autofilter feature

If you’re using the Autofilter feature, you’re probably wondering how to perform calculations on only those values that are being displayed by the filter. The SUBTOTAL() function is the answer.

The syntax of the SUBTOTAL() function is as follows:

=SUBTOTAL(function_num, ref1, ref2…)

where function_num determines what the calculation should be (see below) and ref1, ref2 etc is the range of cells to be calculated.

Possible values for function_num are as follows:

 For example, to add up the values in column H (let’s say this is the Sales Amount column) that are currently visible, enter the following formula:

=SUBTOTAL(9,H2:H20)

This formula will calculate the sum of all the visible values in column H, and assumes that the table has 19 rows, from H2 to H20. If rows 3, 5, 7, 9 and 11 are not visible because they have been filtered out, the subtotal will not include those values. If you then change the filter criteria, then the SUBTOTAL() function will automatically recalculate based on the rows that are visible (i.e. that have not been filtered out).

Based on the table above, it should be easy to see that changing the formula to the following will calculate the AVERAGE rather than the SUM of the visible rows in column H:

=SUBTOTAL(1,H2:H20)

A couple of quick tips on using SUBTOTAL() on filtered lists:

• Data tables tend to to get longer as more rows are added. Consider putting your subtotals at the top of the range not the bottom so you don’t have to keep moving the subtotals down to make room for new rows.
• Don’t forget to update the formula as you add new rows. Alternatively, consider writing the formula to include the empty rows below the table so you can add new rows and have them automatically included in the calculation.
• You can use the Autosum feature to automatically enter a SUBTOTAL() function to calculate the sum of a selected range of cells, but makes sure that the formula enters the correct cell range. The Autosum function will base it’s calculation on the visible cells. If the first visible row is not the first row in the table (i.e. the first row is filtered out) it will not be included when the SUBTOTAL function is created using the Autosum feature.

Lesson Subject – Excel Formulas and Functions
Related Lessons – 13, 16, 27