Excel 2007 SUBTOTAL Formula Explained

If you think SUM is handy, wait til you meet SUBTOTAL.  SUBTOTAL has so many tricks up its sleeve you’ll be mesmerised, but don’t be put off because this formula is simple.

If you’ve ever worked with Filters or hidden rows you’ve probably wanted to find the total of a column excluding what’s hidden. What about finding out the AVERAGE, COUNT, MAX, or MIN excluding the hidden or filtered rows?  SUBTOTAL can do all this and more, and it’s one of the easiest formulas to get your head around.

The syntax of the SUBTOTAL formula in Excel is:

=SUBTOTAL(function_num,ref1,…)

Don’t be put off by the ever helpful syntax.  I said this formula was simple and you’ll see as I translate it into English:

=SUBTOTAL(what type of total do you want, what range/s do you want to subtotal)

function_num is referring to the type of total you want.  You see, SUBTOTAL can be used to SUM, COUNT, AVERAGE and much more.  See the table below for a complete list of the different types of totals you can use SUBTOTAL for.  You simply enter the function_num from the table below into your formula to instruct Excel what type of total you want.

For example if we wanted to SUM a column of filtered data (say our range is C4:C32) our formula would read:

=SUBTOTAL(9,C4:C32)

This formula would give us the total for all values in the range C4:C32 that aren’t hidden by a Filter.

Now you see how easy it is, let’s go over some of the features of SUBTOTAL:

1)      In the table above you will notice there are two columns.  The first column containing function numbers 1-11 will exclude any cells that are hidden by a filter

2)      The second column containing function numbers 101-111 do the same as above, but will also exclude any cells in rows that have been manually hidden.

3)      You can use SUBTOTAL on more than one range. e.g. =SUBTOTAL(9,C4:C32,D4:D32,E5:E7 etc.etc)  Simply separate any non-contiguous ranges by a comma.

4)      SUBTOTAL ignores any other subtotals in the range.  Taking the example below, our Grand Total on row 37 will not include the Night Garden Total, Spider Man Total, or the Wiggles Total even though the range for the Grand Total formula is C21:D36.

Tips and Ideas for SUBTOTAL

• You can automatically insert SUBTOTAL’s (as I did above) using the SUBTOTAL tool on the ‘Insert’ tab of the ribbon.  You’ll find a detailed video tutorial in our Microsoft Office Master training program on how to insert a SUBTOTAL automatically.

• Why not put the SUBTOTAL formulas at the top of your data.  This is handy if your data goes on for thousands of rows and you don’t want to be scrolling to the bottom to see the totals all the time.

• Set your SUBTOTAL formula up to dynamically update for any new rows added to the bottom of your data by using the OFFSET function nested in your SUBTOTAL, or link the SUBTOTAL formula to an Excel Table.

Don’t forget you can get more free Microsoft Office online training and video tutorials from My Online Training Hub.  Just type your name and email address in the form below and click the ‘Get Free Access’ button for instant access to our free online training.

Share the knowledge with your friends and colleagues on Twitter, and facebook etc. using the shortcuts below.

Got a clever way you use SUBTOTAL?  Let us know by leaving a comment.

Tagged as: excel subtotal, subtotal, subtotal formula