Posted by Michael Bowen | Case Management · Database · Excel · Software · Tips | Blog
[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:
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.
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.
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:
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:
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:
And to see the total for all 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:
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:
There are lots of other functions that the Subtotal can perform…:
…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.