Bonus Tip On Using The SUBTOTAL Function In Excel

Recently I did a post on the SUBTOTAL function, but since then I learned a new feature of this useful tool. Let’s take a look at this bonus feature of the SUBTOTAL function in Excel.

The link to the previous blog post on SUBTOTALS in here.

Here I have two sets of data. On the left, I used the SUM function to add the totals of the two groups of Products. On the right I used the SUBTOTAL function, using the option “9” for summing the values:

Now, if I use the SUM function on the left group to obtain a grand total, Excel will automatically include the intermediate totals and I will get a value double what I want:

This will the require me to change the Grand Total formula to =G12+G24 :

Now the Grand Total is correct.

However, on the right set of data, if I use the SUBTOTAL function with the “9” option to do the same, I will get the correct value:

The fact is that the SUBTOTAL function ignores other uses of the SUBTOTAL function when it totals the values in a range! That’s a great feature that can be highly useful when you have data that is being subtotaled, or, as in our next example, may require future modification.

For example, let’s say that I want to insert a subtotal in the data range to find out what the sum is of the first five Products:

Notice that using the SUM function included that subtotal of the first five Products in the Grand Total, while the range using SUBTOTAL ignored that subtotal value and left the Grand Total equaling the correct amount!

With SUBTOTAL around, you may never use the SUM function again!

What can you do next?