Excel: Use AutoSum After Filtering

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I need to total only the visible cells in a filtered data set.

Strategy: You can use the AutoSum icon after applying a filter. Normally, the AutoSum icon inserts a SUM function. When you apply a filter and then use AutoSum, Excel will insert a SUBTOTAL function instead. This function will ignore rows hidden by the Filter command. Follow these steps:

  1. Choose a cell in your data set. Select Data, Filter.
  2. Apply a filter to at least one column. Open the Customer dropdown and choose one customer.
  3. Select the first visible cells beneath your numeric columns. Below, the last visible row is 539, but the next blank cell is in row 568.
  4. Click the AutoSum icon and press Enter. Excel inserts a SUBTOTAL function that uses the correct syntax to skip rows hidden by the filter.

  1. Excel inserts the SUBTOTAL function.

    When you choose a different selections from the filter dropdown, the SUBTOTAL function will show the total for those visible.

    Additional Details: During a seminar for the Fort Wayne IIA, someone added a great suggestion to this topic. Insert two blank rows at the top. Cut the formulas from the total row and paste to row 1. Now, even if the filtered rows are more than will fit on a screen, you always have the filtered totals at the top.