Excel’s basic functions, like SUM, AVERAGE, COUNT, MIN and MAX are indispensable for harnessing the power of spreadsheets, but they don’t always work well with filters and structured reports. By default, these functions tally every cell in their ranges, regardless of whether the cells are filtered or hidden. Many times in reporting, it is useful to limit functions to only consider rows that are visible on the sheet and ignore the hidden or filtered values. SUM, AVERAGE, and the like can’t do this on their own. Fortunately, Excel has a function called SUBTOTAL that will consider only visible or filtered rows in its calculations, and it can perform all the operations mentioned and more! This tutorial will walk though the use of the SUBTOTAL to sum a filtered data table…
Examine the Data Set
First off, let’s take a look at our sample data…
We have basic sales data by Region, Sales Manager, and Year. Ideally, we’d like to be able to filter by Region to get a total for sales in that part of the country. Usually, it is easiest to filter data when it is in a data table, so let’s create one.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!