If you have a list of invoices in Excel, and you want that list to show a total for each month and for the year, most people would use the SUM function to total each month. But if you try to do that for the year you will end up totaling both the invoices and the monthly totals, unless you move the monthly totals to a separate column. Another common approach is to write a formula that points to each monthly total and adds them up. The beauty of the SUBTOTAL function is that you can add up the whole column and it will ignore the other SUBTOTALS that finds.
Here is an example of how it works:
Let’s say that I want a subtotal in cell C4 that adds up the 3 cells above it. I would enter the formula =SUBTOTAL(9,C1:C3) in cell C4. As you would expect, the “C1:C3” designates the range of cells from C1 through C3. I’ll explain the 9 later.
Next I want another subtotal in C9 that adds up the 3 cells above it. I would enter the formula =SUBTOTAL(9,C6:C8) in cell C9.
Now, to put a grand total on line 11, I can use the formula =SUBTOTAL(9,C1:C10). Notice that the range doesn’t exclude cells C4 and C9 where the other subtotals are. The subtotal function automatically excludes those amounts.
In a small example like this, it may not seem worth the trouble of trying to remember how to enter the subtotal function. But in a large spreadsheet with hundreds or even thousands of lines, you can save a lot of time and effort by not having to track down the individual ranges that would be needed to use the more familiar SUM function. Next month I will show you how, in many cases, you can have Excel insert the subtotals and grand total automatically, so you don’t have to remember how to enter the subtotal function yourself.
Now, back to the mysterious “9” that I said I would explain. The subtotal function has 11 different options that can be chosen. Among other things, it can add, multiply, count, or average, the entries in a given range of cells. The 9 simply tells Excel to add or Sum the cells in the range. For a complete list of options, search for SUBTOTAL FUNCTION in Excel’s help.
