SUBTOTAL Function in Google Sheets

Adam Steinfurth

SUBTOTAL deserves more attention than it gets. Despite its robustness, it is far less prevalent than functions such as SUM and AVERAGE. Let’s look at what you can do with this function in Google Sheets using this template to follow along.

Purpose

SUBTOTAL returns an aggregate result for a range of cells based on the specified function code.

Video Explanation

YouTube player

Syntax

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

  • Function codes:
  • 101 – AVERAGE*
  • 102 – COUNT*
  • 103 – COUNTA*
  • 104 – MAX*
  • 105 – MIN*
  • 106 – PRODUCT*
  • 107 – STDEV*
  • 108 – STDEVP*
  • 109 – SUM*
  • 110 – VAR*
  • 111 – VARP*
  • * All three-digit codes ignore hidden rows.
  • range1 – The first range for which to calculate the selected function.
  • range2… – [OPTIONAL] Additional ranges to calculate the chosen function.
  • function_code – The function to use for the aggregation.

The function code is a predefined constant representing what operation you wish to perform. You can apply any of the 11 operations within the functions listed above.

Alternative to SUBTOTAL

For an alternative method to deal with different levels of aggregation, spreadsheet.com enables the creation of row hierarchies, allowing you to nest levels of data to create parents, children, ancestors, and descendants. Read more about this feature to see if it could help you.

Example 1 – Summing a Range with Subtotals

There are many situations where the SUM function falls short, in which case the SUBTOTAL with a function code of 9 acts as a substitute. Let’s look at an example. If you had data containing subtotals for different categories and you wanted to find the total, you would have a few options at your disposal.

For example, you could use the + operator to add the cells one by one =cell1+cell2+cell3+... which could be tedious. Similarly, you could use the SUM function, which is susceptible to double counting. Instead, we could use SUBTOTAL to make the job much easier.

Our number of interest would be 9 because 9 corresponds to SUM. As a result, the formula would be =SUBTOTAL(9, range1, [range2],...). There is one caveat, however. You have to calculate the smaller totals first using the SUBTOTAL function. Take a look at the following dataset:

A SUBTOTAL incorrectly double counting two other subtotals
Incorrect total

We can see that the above grand total in cell B18 is too large. SUBTOTAL adds all values in the specified range regardless of whether they are a total. This double-counting isn’t helpful to us and is quite similar to what the SUM function would do. How can we fix this?

We have to calculate the totals in cells B7 and B15 using the SUBTOTAL function with a function code of 9 so that the grand total will ignore these total cells. After making this switch, this is what we get:

A SUBTOTAL correctly counting two other subtotals
Correct total

Nice! The formula now gives us the correct total.

Example 2 – SUBTOTAL with Filtered Data

Using filters in your spreadsheet, the SUBTOTAL function will treat the filtered data differently than the underlying function, such as AVERAGE. The AVERAGE function will include filtered rows in its calculation.

The AVERAGE function treating cells the same as the SUBTOTAL Function with function code 1

The SUBTOTAL function will exclude filtered data using only the visible rows after filters are applied. You can see this difference below because AVERAGE is still returning $30,162 while SUBTOTAL with the function code 1 is returning $37,322.

The AVERAGE function treating filtered cells differently than the SUBTOTAL Function with function code 1

Note that AVERAGE is one of the eleven functions available, and this different treatment of filtered cells occurs for all eleven.

Example 3 – Treatment of Hidden Rows

While example 2 dealt with filtered rows, a spreadsheet user can also hide selected rows. You can hide rows by right-clicking on the desired rows and choosing the Hide rows menu option.

Illustrating how the function treats hidden rows, this third example will use function codes 4 and 104. These codes call the MAX function, which returns the highest value in a data set. 4 will include the values in hidden rows and 104 will exclude them. In the first image, both function codes, 4 and 104, return the highest number in the range of $39,60.

SUBTOTAL Function Codes 4 and 104 Returning Different Results
Both Functions Returning the Same Result

While the image above shows all rows, the table in the picture below has row 9 hidden. Row 9 is also the row with the largest sales amount in the table. Therefore, function code 104 returns $37,600 as the maximum amount instead of $39,678 because 104 instructs the spreadsheet to exclude row 9 from its output.

The SUBTOTAL Function Codes 4 and 104 Returning the Same Result
Each Function Returning a Different Result

Conclusion

As you can tell in the preceding examples and this template, the SUBTOTAL function provides flexibility and additional capabilities that other functions do not.