Easy Guide to the SUBTOTAL Function in Google Sheets

When working in Google Sheets, you may want to periodically check the subtotal of your data set or include results in the final documentation.

The SUBTOTAL function is the easiest way to accomplish this. On top of calculating subtotals for data lists, the subtotal function also:

• Calculates metrics with hidden or filtered data rows
• Dynamically selects functions

This is an underutilized function due to its tricky nature. It’s not exactly beginner-friendly due to having to use function controls to operate. However, if you follow this guide, you should be fluently working with the SUBTOTAL function in no time.

What is a Function Control?

Function controls tell a particular function in Google Sheets how to behave. In the case of the SUBTOTAL function, you may want to find the SUM, AVERAGE, MAX, etc.

Using function control code in the syntax tells the SUBTOTAL function which calculation it should be using.

It may sound a little confusing, but the cheat sheet below will help you. You can check back to it while you’re working on your spreadsheet. There is no need to memorize the function controls straight away.

You could even create a dynamic function selector so you don’t have to memorize each code.

SUBTOTAL Function Controls

As you can see in the table, there are eleven function controls for the SUBTOTAL function in Google Sheets. Each of these function controls can either include or exclude hidden values.

One-digit or two-digit function controls include hidden values. Three digits in the function control argument mean the hidden values are excluded.

Let’s take a look at how the function controls fit into the syntax.

SUBTOTAL Syntax

=SUBTOTAL(function_code, range1, [range2, ...])

You’ll need a minimum of two arguments for the SUBTOTAL function to work. The first is one of the function controls listed above and the second is the range of data.

However, you can add as many data ranges as you’d like to this function.

Let’s use the following arguments in the syntax as an example.

=SUBTOTAL(9, B2:B7)

Since the first argument in the SUBTOTAL function is a control, it tells the function to apply the desired aggregation. In this example, 9 is the chosen function control.

If you look at the above table, you can see that using 9 in the syntax will apply the SUM function to the given range of B2:B7, including any hidden values.

To exclude any hidden values, you would have to use the equivalent three-digit function control of 109 instead.

In the next screenshot, you can see we added a second set of data to the subtotal.

This will include the set of data from A2:A7 in the calculation too.

Note that the square brackets “[“ given in the sample syntax are not necessary and will cause an error if entered into the formula. They just signify the start and finish of the ranges you could enter in the syntax and are not actually part of the calculations.

Why You Should Use the SUBTOTAL Function

It may seem easier to use the standard built-in function for each subtotal calculation instead. After all, in our first example, you could have just as easily used the syntax of =SUM, (B2:B7) and still received the correct answer.

While this is true, it’s the more complex spreadsheets that would benefit from using the SUBTOTAL function instead.

In the following example, the SUM function has been used to calculate the subtotals and grand total on the spreadsheet.

You can see that the subtotals are correct using the SUM function, but the grand total is wrong. This is because it calculated the subtotal figures as well as the other numbers in the column that created the subtotals.

You could avoid this by using the syntax for the SUM function of =SUM(C9+C18+C27) to get the correct answer. But, this is a much slower and more tedious way than using the SUBTOTAL function in the first place. It’s also much easier to make a mistake by manually clicking or typing each field.

Here’s how it looks using the SUBTOTAL function for the same dataset.

You can see that the grand total now has the correct answer of 70. This is because the SUBTOTAL function ignores the duplicate data while the SUM function does not. It does so by ignoring every other SUBTOTAL function in the given range.

This concept applies to every comparable function control used by the SUBTOTAL function, not just SUM.

Interactions of the SUBTOTAL Function with Filtered and Hidden Data

Take a look at the following data set.

In it, we have used the following formulas in each of these cells.

• C15 =SUM(C2:C13)
• C16 =SUBTOTAL(9,C2:C13)
• C17 =SUBTOTAL(109,C2:C13)

As you can see, each of these formulas gives the same correct answer for the total number of sales. But, when you start filtering or hiding data, each formula will interact differently with the spreadsheet.

Filtered Data

In this next example, we have filtered by brand to only show the sales of one.

You can see that both of the SUBTOTAL rows have removed any filtered data from their calculations but the SUM function has not. This same concept would apply if we filtered the year column. This would have given the subtotal by date instead.

So, make sure you choose the right function as to whether or not you want to include filtered data in the totals of your spreadsheet.

Hidden Data

For the last example, we added a hidden row to the data set too. Notice that the figure in cell C17 changes. This is because we used the function control of 109, which ignores hidden rows. The same would apply to hidden columns.

Make sure you pick the right function control and subtotal with condition parameters to apply to the end goal of your spreadsheet.

Conclusion

At first glance, the SUBTOTAL function can be a little intimidating. Yet, once you wrap your head around the function controls, you will save a ton of time while working on your spreadsheets.

If you found this tutorial on how to count unique subtotals in Google Sheets useful, you may also be interested in learning about running totals.