#164 Excel Subtotals

February 19, 2010
Fasten your seat belts because this week we’re going to dish out an extra large and powerful dose of Excel ‘magic’. I’m a HUGE fan of the AutoFilter tool in Excel. If you’re not familiar with it, filtering gives you a quick, easy way of paring down your data to focus on only the stuff that matters. Go ahead and take a look here….we’ll wait ’til you get back. (Really, go ahead!)

OK. Welcome back. We now continue with our regularly scheduled programming.

AutoFilter by itself can be a big time-saver and by paring it with the SUBTOTAL function you can turn yourself into an Excel Superhero. The SUBTOTAL function lets you do dynamic calculations that change based on only what is displayed by your AutoFilter settings. That means if it’s not visible, it’s not included in the calculation. Here’s a sneak peak…

The syntax for the SUBTOTAL function is as follows:

SUBTOTAL(function, refs)

In this syntax, the function is a value of 1 through 11, each representing a different way that SUBTOTAL can analyze your data. The settings have the following meanings:

Function Meaning
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

How It Works:

Using this sample data I can create one set of formulas to calculate stats on ANY combination or the data I want.

[ Download a copy of my spreadsheet to play with yourself ]

Here are the formulas I created:

..and the results of those formulas when the AutoFilter is displaying all the data.

Same formulas but different results with the AutoFilter displaying only the Asia Region: