Excel Tip: Subtotal()

The purpose of the subtotal() function is to provide real subtotals, without double counting.  The secret to the subtotal() function is that a subtotal() function excludes all other subtotal() functions in its range.  That is the key concept of this function.  It excludes all other subtotal() functions in its range.

There are two function arguments to the subtotal() function.  The first argument is the type of math.  Microsoft selected the number 9 to be "add".  If you wanted instead to take the average of the range, your first argument would be 1.  For the others, check the Excel help.  Most of the time though, we'll want to add, so we'll pretty much use 9.  So, our typical subtotal() function will be written like this: =subtotal(9,RANGE), where RANGE is the range you want to add.

I made a little short video that demonstrates subtotal() working, and you can check it out below.