Using the SUBTOTAL Function with AutoFilter: MS Excel

When AutoFilter is activated, Excel recognizes that a calculated total should reflect only the record that are currently displayed in the data list. For example, in a data list of business expenses with an Amount field, it would be possible to create a formula at the bottom of the field that reflects the total amount of only those amounts displayed by the filter. The SUBTOTAL function is used to calculate a total amount based on a filter.

Follow these steps to use the SUBTOTAL function with AutoFilter:

1. Click in a cell in the column to subtotal.
    
2. Click Sort Ascending or Sort Descending.
    
3. On the Data menu, click Subtotals.
    
4. In the At each change in box, click the column to subtotal.
    
5. In the Use function box, click the summary function.
    
6. Summary function is a type of calculation that combines source data in a PivotTable Report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count and Average.
    
7. In the Add subtotal to box, select the check box for each column that contains values you want to subtotal.
    
8. If you want an automatic page break after each subtotal, select the Page break between groups check box.
    
9. If you want the subtotals to appear above the subtotaled rows instead of below, clear the Summary below data check box.
    
10. Click OK.

You can use the Subtotals command again to add more subtotals with different summary functions. Make sure that you clear the Replace current subtotals check box so as not to overwrite your existing subtotals.

When you become a member at CarolsCornerOffice.com, you have access to this and many, many more articles that include screenshots. Don't delay: visit us today!