How to Use the SUBTOTAL Function in Google Sheets

How to Use the SUBTOTAL Function in Google Sheets

The SUBTOTAL function in Google Sheets is a powerful and versatile function that can perform different calculations on a range of data, depending on the function code you specify. It can also ignore hidden or filtered rows in your data, making it useful for creating subtotals and summaries. In this article, you will learn how to use the SUBTOTAL function in Google Sheets, with examples and tips.

What is the SUBTOTAL Function in Google Sheets?

The SUBTOTAL function in Google Sheets has the following syntax:

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

It takes two or more arguments: first the function code, then at least one range of data to operate on. The function code is a number that determines what operation the SUBTOTAL function will perform on your data. For example, the number 9 corresponds to the SUM function.

There are 11 different function behaviors accessible with the SUBTOTAL and for each, you specify whether to include or ignore any hidden rows of data. If the function code number is between 1 – 11 the hidden rows are included in the calculation. If the function code number is between 100 – 111 the hidden rows are ignored in the calculation.

Note: rows of data that are filtered out are never included in a SUBTOTAL, regardless of the function code.

Here are all the options available for the function code option:

Aggregation	Code, including hidden values	Code, ignoring 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

How to Use the SUBTOTAL Function in Google Sheets: Examples

Example 1: Calculating Subtotals for Lists of Data

Suppose you have the following dataset, where each sub-table has a subtotal using the SUM function:

If you calculate the grand total using the SUM function, you risk double counting the revenue. The SUM function adds the revenue values AND the subtotals, meaning your total will be twice what it should be. This is BAD!

To fix this, you have to manually select the subtotal values and sum them with a formula like this:

= C6 + C13 + C20

This isn’t ideal because it’s tedious to select each one and easy to make a mistake.

However, by using the SUBTOTAL Function in Google Sheets, you can solve this problem. Replace each of the SUM formulas with formulas using the SUBTOTAL function, e.g.:

=SUBTOTAL (9,C2:C5)

When you calculate the grand total, again using the SUBTOTAL function, it won’t double count the values. It will only sum up the subtotals that are visible, ignoring any hidden rows.

Example 2: Calculating Metrics with Filtered and/or Hidden Rows of Data

Another use case for the SUBTOTAL function in Google Sheets is to calculate metrics that take into account filtered and/or hidden rows of data. For example, suppose you have a table of sales data with columns including “year”, “industry”, “total revenue” and “revenue per employee”. The table has filters so you can look at the average revenue per employee for different industries for example.

![Example data]

If you use a regular AVERAGE function to calculate the average revenue per employee for each year, it will include filtered rows in its calculation. For example, if you filter by industry = “Technology”, the AVERAGE function will still use all the rows in column D for its calculation, even though some of them are not visible.

![Example problem]

This is not what we want. We want to calculate the average revenue per employee only for the visible rows after applying the filter. To do this, we can use the SUBTOTAL function with a function code of 101, which corresponds to AVERAGE while ignoring hidden rows.

=SUBTOTAL (101,D2:D21)

This way, we get a more accurate result that reflects our filter criteria.

![Example solution]

You can also use other function codes with the SUBTOTAL function to calculate other metrics such as count, max, min, etc. depending on your needs.

Tips and Tricks for Using the SUBTOTAL Function in Google Sheets

• You can use multiple ranges as arguments for the SUBTOTAL function, separated by commas. For example, =SUBTOTAL (9,A1:A10,B1:B10) will sum up the values in both ranges A1:A10 and B1:B10.
• You can use the SUBTOTAL function inside another SUBTOTAL function to create nested subtotals. For example, =SUBTOTAL (9,A1:SUBTOTAL (9,B1:B10)) will sum up the value in A1 and the subtotal of B1:B10.
• You can use the SUBTOTAL function as a dynamic function selector, by using a cell reference or a formula as the function code argument. For example, =SUBTOTAL (A1,B1:B10) will perform the operation specified by the value in A1 on the range B1:B10. You can change the value in A1 to any of the function codes to change the behavior of the SUBTOTAL function.
• You can use the SUBTOTAL function with other functions such as FILTER, QUERY, SORT, etc. to perform more complex calculations on your data. For example, =SUBTOTAL (101,FILTER (D2:D21,B2:B21=“Technology”)) will calculate the average revenue per employee for the technology industry only.

Conclusion

The SUBTOTAL function in Google Sheets is a useful function for working with data that has subtotals, filters, or hidden rows. It can perform different calculations on your data depending on the function code you specify, and it can ignore hidden or filtered rows in your data. You can use it to create subtotals and summaries, as well as other metrics that take into account filtered and/or hidden rows of data. You can also use it with other functions to create more advanced formulas.

Tweet