The SUM function is the function of choice to add up values. But when it comes to subtotalling, SUM leaves a lot to be desired. There is a specific function for subtotalling called, funnily enough, SUBTOTAL.
The SUBTOTAL function ignores other SUBTOTAL functions in its range. This means it is easy to create a Grand Total that automatically handles extra subtotals being added within the range.
The syntax or layout of SUBTOTAL is
=SUBTOTAL(function number, Reference)
The function number is number that determines what function will be applied to the reference range. To SUM you use 9. Other numbers do other functions eg 1 will AVERAGE and 3 will COUNTA.
An example of a SUM SUBTOTAL is
I typically use the SUBTOTAL if there is any possibility that there will be new subtotals required above the grand total.
The SUBTOTAL Example file (bottom of blog) contains methods people use to do subtotals and demonstrates the SUBTOTAL function.
If you have a data list you can use Excel’s automated subtotaling feature to insert SUBTOTAL functions and Grouping for you.
The secret is to first sort the list by the field you want to subtotal by and then click the Data menu or Data Ribbon tab and choose Subtotal.
See an example of a dialog to create the subtotals below.
An example with instructions is included in the SUBTOTAL Example file at the end of the blog.
The other thing that SUBTOTAL can do that SUM can’t, is to add up visible cells only. This means that if you hide rows or columns the hidden values will not be included in the SUBTOTAL. To SUM only visible rows you use 109 instead of 9 in the SUBTOTAL eg
This applies to filtered lists, and can be handy to do a SUM or COUNT of a filtered list – example included in the file below.