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

=SUBTOTAL(9, B2:B20)

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.

Automated SUBTOTALS

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.

Hidden ability

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

=SUBTOTAL(109, B2:B20)

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.

SUBTOTAL Example