Subtotal

Excel® is a robust computer program for multiple tasks, from creating personal budgets and supplemental accounting documents to troubleshooting accounting software and even programming some software. With all that it offers, many of us still only use a fraction of its features. This series — Excel Pro Tips — covers some of the best formulas, tables, conditional formatting, and other processes to benefit users. Today’s article looks at the Subtotal function, a better way to sum.

Background

With some reports and financial statements, such as the income statement, we will typically have a series of categories followed by a subtotal for the series. This is repeated a number of times. At the very bottom of the statement or report, there is a total, where all the numbers above are added into one finalized, nicely packaged number. For the longest time, I used the sum formula in Excel. I would do =SUM(B1+B2+B3) or =SUM(B22:B32) to come up with each series subtotal, and then the total formula would reference each sum formula from above. As time moves forward, categories were added or deleted, and the formulas would get messed up and need to be repaired. There is a better way to handle this situation.

Subtotals

Excel, believe it or not, actually made a formula called Subtotals. It works much like the sum formula with one major advantage. The subtotal formula adds all numbers in the cell range that are not other subtotals. So, if I use the subtotal formula for each series of categories, it will add the numbers just like the sum formula does. When I get to my last total formula though, I am not going to reference only the subtotal cells. I want to reference every category in the series. If my category numbers and subtotals were in cells C3 to C87, my formula for the total in C88 would be =SUBTOTAL(9,C3:C87). That formula will add every number it comes across, but skip any numbers that are the result of another subtotal. That is fantastic, right?

What is the Deal with 9?

Some may have caught that the subtotal formula above has the number nine before the cell reference. What is the nine for?  Let me explain the formula a bit more. The subtotal formula is best thought of as a label. This label allows me to do fun things like those mentioned in the previous section. Subtotal in Excel is not a mathematical process.

With each subtotal formula, we need to specify what mathematical operation the subtotal needs to perform. That is where the nine come in. If you start the formula in Excel and type =SUBTOTAL(, a suggestion box will pop up next to the formula. The box will have suggestions such as 2 – Count, 4 – Max, and 9 – Sum. These are the mathematical functions the subtotal can calculate. So, if I wanted to find the maximum in the number series, I would use the number four, =SUBTOTAL(4,B3:B32). In the very next cell, I could do another subtotal function to add the series, and because my maximum formula has the subtotal label, I will not have to worry about that particular number getting included.

Give this formula a try!  Please email me at aduerfeldt2@unl.edu with topics for future articles.