To quickly count how many veggie dogs you need to buy based off the number of veggie dogs eaten last month, add a
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.
For June, therefore, your function would read: =SUBTOTAL(B15, D2:D14)
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.
Posted by: Lai Kwan Wong, Software Engineer
