How to use the SUBTOTAL Function in Excel – how does it differ to the SUM function?

How to use the SUBTOTAL Function in Excel – how is this function different to the SUM one in Excel

This week’s hint and tip is on SUBTOTAL function in Excel. This function is used for working out totals in Excel but with the added benefit of excluding filtered rows by default and having an option to also exclude hidden rows. We don’t necessarily cover this function in our courses, so we decided to do a hint and tip on it. We are going to go through it now below.

SUBTOTAL Function

The SUBTOTAL function is a useful function in Excel as it allows you to choose from a list of functions you want to subtotal a range of cells by. This function is often seen as being better in some ways than the SUM function because it excludes filtered out rows by default and also has the option to exclude hidden rows.

The syntax for this function is shown below:

=SUBTOTAL(function_num,ref1,[ref2],…)

The parts to this function are explained below:

• function_num: this is required. This number refers to the function that you want to use for your range. Numbers 1-11 are the function including manually hidden rows, whereas numbers 101-111 are the function but excluding manually hidden rows
• ref1: this is required. This part of the function refers to the range of cells you want to apply the subtotal to
• [ref2],…: this is optional. This is where you can put in further cell reference ranges depending on what cells you are using in your function

The list of numbers to type in and the functions they refer to is shown below:

1. AVERAGE (101 – to exclude manually hidden rows)
2. COUNT (102 – to exclude manually hidden rows)
3. COUNTA (103 – to exclude manually hidden rows)
4. MAX (104 – to exclude manually hidden rows)
5. MIN (105 – to exclude manually hidden rows)
6. PRODUCT (106 – to exclude manually hidden rows)
7. STDEV (107 – to exclude manually hidden rows)
8. STDEVP (108 – to exclude manually hidden rows)
9. SUM (109 – to exclude manually hidden rows)
10. VAR (110 – to exclude manually hidden rows)
11. VARP (111 – to exclude manually hidden rows)

Worked example

In the video below, we go through a worked example. In this, you can see the difference between the SUM function being used and also the SUBTOTAL function being used with both variations of number references shown above.

The screenshot below shows the full list of data with the answers to the functions.

This next screenshot, shows the three answers again but shows the difference between the SUM and SUBTOTAL functions. The data has been filtered here and the answers show how the SUBTOTAL function excludes filtered out data in it’s calculation whereas the SUM one doesn’t.

This next screenshot shows the answers for when rows are manually hidden. You can see in the answers that the SUM and one of the SUBTOTAL function answers doesn’t update. However you can see that the SUBTOTAL answer, using the function number 109, does update as this one also excludes manually hidden rows as well when doing the calculation.

The video below goes through examples of how the SUBTOTAL function updates in different situations compared to the SUM function which does not. This means you can decide which function you want to use based on what you might be doing with your data. Take a look below at the video to find out more and then try it out on your own computer!

We hope you have enjoyed this hint and tip on the SUBTOTAL function in Excel. Why not take a look at our previous video hint and tip on how to use the document formatting options in Word?