Easy Guide to the SUBTOTAL Function in Google Sheets

By

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

Formula used

Code (including hidden values)

Code (excluding hidden values)

Average

1

101

Count

2

102

Counta

3

103

Max

4

104

Min

5

105

Product

6

106

Standard Deviation

7

107

Standard Deviation Population

8

108

Sum

9

109

Variance

10

110

Variance Population

11

111

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)

subtotal function

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.

second data set

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.

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.

using the SUBTOTAL function for the same datasetsubtotal 70

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.

shoe brand sales 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.

data filtered by brand

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.

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.

Popular Posts

Access All

Free Templates

Enjoy our custom-made Google Sheets templates for free via email access