Excel Tips: Using Autofilters and Subtotal() – Part Two

Posted by | · · · · | Blog

Michael Bowen

[In Part 1 of this series, we discussed how to use Autofilters to manage large sets of data, enabling you to work with only the records that are relevant. In this article we will explore how the Subtotal function in Excel can leverage the power of filters even more.]

Sample Data

Our sample data set for this article is the same as the first:

Sample Data

Part Two: Subtotals

If you’ve used Excel at all, then you are probably familiar with how to sum a column or a row of numbers. What you may not know is that the Subtotal() function allows for much more than basic mathematical manipulation of data, and can be combined with additional functions to perform those manipulations in a more dynamic manner.

Below is a screenshot illustrating just a few of the additional functions that can be paired with Subtotal(), with each option numbered to serve as a clear visual reference for the examples that follow.

Additional Subtotal Filters and Functions

Now, back to our data.

In this sheet, we see that there are 5 records, only one of which is male. Because we used the Sum() function in cell C9 (“=SUM(C4:C8)”) we can easily see that the total of the Financial Outcome column (cells C4 through C8) is $1,750.

Sample Subtotals

In this screenshot, after filtering, we see that the same records have been filtered on Gender, leaving only the record in row 4 displayed. But the Sum() function in cell C9 is still reporting that the sum of cells C4 through C8 is (still) $1,750:

Sample Subtotal with Filter on "Male"

But instead of using the Sum() function, we can use the Subtotal() function to see what happens. Instead of just “summing” the values in those cells, we ask it to do it selectively. This is what the Subtotal() function does.

By using the formula “=SUBTOTAL(9,C4:C8)” instead of always getting $1,750, the “total” will change and it will show you the sum of only the records that are being displayed based on the filters. So, to see the sum of all Financial Outcomes for all Males, the results looks like this:

Sample Subtotal with Filter Adjusted Males

To see the total for all females, there is no need to change the formula, simply change the filters and now you will have this:

Sample Subtotals with Filter on "Female"

And to see the total for all Hispanic Females:

Sample Subtotal with Filter Adjusted for Hispanic Females

This is a very powerful feature that is easy to learn and use.

But wait, there’s more! (And no, it’s not a Ginzu knife!)

Often, instead of summing a column, you just want to know how many records there are. By adding additional variables to the Subtotal() function, we can easily do that.

Using the example immediately above, the logical flow of the function ” =SUBTOTAL(9,C4:C8)” is as follows:

  • Function: Subtotal
  • Type of Subtotal: 9 (sum of all records being displayed)
  • Range to Sum: Cells C4 through C8

To COUNT the records instead of summing the dollar amount, we would simply change the “9” above to a “3” so that instead of:

=SUBTOTAL(9,C4:C8)

…it should look like this:

=SUBTOTAL(3,C4:C8)

Which gives you this end result:

Sample Subtotal with Filter Adjusted to Count Hispanic Females

There are lots of other functions that the Subtotal can perform…:

Additional Subtotal Filters and Functions

…but the Total (9) and the CountA (3) are the most frequently useful ones. (Note: the difference between Count and CountA is that Count counts every cell in the range, CountA only counts the cells that are not blank. This will make sense when you care about this distinction.)

I hope this will give you a little bit of insight into how these two simple functions can really help you take a fresh look at your data. Excel has some extraordinary features, but the Autofilters and the Subtotal functions, when used together, can be a great yet simple entry into the uses of this amazing tool.