Subtotals – A Trick I Didn’t Know

After all of these years, I learned a new trick that you can use with the SUBTOTAL function.

I’ve been using SUBTOTAL as a form of SUM, but you can also use it for counting or averaging as well as other statistical type actions. SUBTOTAL(9, “range”) gives you the sum whereas SUBTOTAL(1, “range”) gives you the average and SUBTOTAL(2, “range”) gives you the count. I’ve been using it with the filter to sum up (or count up or average up) only those items you see on the screen. If you use SUM function with the filter, the function will also include the hidden rows in the summing. SUBTOTAL gets around that.

Now, it appears SUBTOTAL has another really nifty feature. Say you have a table showing multiple categories and within those categories, detailed lines. A category could be payroll, maintenance, or travel expense. The detailed lines could be manager, full-time employee, subcontractor under payroll category; software, hardware, car under maintenance; car mileage, hotels, airfare under travel. Sometimes such reports will show the subtotals for each categories and then a grand total at the bottom. You can’t use a SUM function to sum down a column (could be row but columns are generally the preferred format); otherwise, you would be double counting. You have to add each category subtotal separately to get a grand total and this can become a tedious manual process.

The SUBTOTAL function gets around this. If each category’s subtotal was summed up using SUBTOTAL(9, “range”), then you can use the SUBTOTAL function for the grand total. The function is smart enough to know to exclude all of the subtotals in the grand summation.

An additional feature of SUBTOTAL is that using 9 will include hidden (not “filtered” – apparently that’s an entirely different action) rows in the summation but if you use SUBTOTAL(109,”range”), then the function will exclude hidden rows in the summation. The way the SUBTOTAL works with the filtering (as opposed to hiding rows) remains the same.

Why didn’t I know this before?