Tips & Tricks: Using the new Subtotal function in Google spreadsheets
September 1st, 2011 | Published in Google Docs
This week, we added the Subtotal function to our list of functions in Google spreadsheets. One of the benefits of the Subtotal function is that it works well with AutoFilters by only using unfiltered data when performing calculations (other functions such as Sum include filtered data calculations). Subtotal also lets you change what function you’re performing on those values very quickly, by selecting an item from a drop-down list. See our help article for details.
This versatile function is often used by accountants, finance professionals, and business consultants. It can also be extremely convenient for any user -- let’s show you why.
Say that you’re helping to plan your family’s annual Labor Day beach weekend. You want to decide how many hot dogs and veggie dogs to buy. To figure this out, you create a Google spreadsheet that includes all your family members, their meat preferences, and the number of hot dogs everyone ate at the past several family gatherings:
To quickly count how many veggie dogs you need to buy based off the number of veggie dogs eaten last month, add a filter to the columns , sort to “Yes” only in Column C, and type in this Subtotal function underneath the table:
=SUBTOTAL(109, F2:F14)
Cells F2 through F14 show the number of hot dogs each family member ate last month. “109” is the code that references the Sum function (“9” would also work). Typing in a regular Sum function in this case (=SUM(F2:F14)) would have added all dogs, veggie or not, whereas Subtotal ignores hodogs which have been filtered.
Another neat feature of the Subtotal function is that the function code (such as “109” above) can easily be changed to refer to different operations like Average, Minimum, and Maximum. As a result, Subtotal can be used to condense a number of calculations into a small space.
Let’s say you want to see not only the total number of hot dogs eaten each summer month, but also the average number eaten. Rather than creating two different functions (Sum and Average) for each month, you can use Subtotal.
- In an open cell -- let’s use B15 -- you would create a drop-down list with the codes for the Sum and Average function (109 and 101 respectively).
- And under the column for each month, you would write a Subtotal function, but reference cell B15 instead of typing in a code.
Every time you change which code appears in cell B15 through the drop-down, the values under each month will change, showing either the total or the average number of hot dogs eaten by your family with just one click.
We hope the Subtotal function makes your data analysis a lot easier -- and maybe even more fun.
Lai Kwan Wong, Software Engineer