SUBTOTAL is a function in Google Sheets that can be incredibly versatile. With the right tips and tools, it can be dynamic and powerful, allowing you to exponentially increase your sheets’ functionality.
When using the Google Sheets SUBTOTAL function, you will find various ways to use it and multiple applications with reporting.
The three primary ways to use it are:
- Applying various essential functions (i.e., Sum, Count, Average) to a list of data.
- Creating a reporting selector to view different stats from one set of data.
- Calculating data with or without hidden rows.
This function can intimidate new or untrained users as it requires a function code to operate, but as you will see, there are ways to utilize its power without memorizing the function code list.
Once you complete this tutorial, you will have no reason to worry about using subtotals in Google Sheets, as you will fully understand its versatility and simplicity.
Let’s get started.
Google Sheets SUBTOTAL Function
We will begin with the syntax for SUBTOTAL in Google Sheets.
The SUBTOTAL function requires two or more arguments.
- Function Code.
- At least one range to perform the function on.
The function codes are pictured below and are available in Google Sheets at any time. To do this, you will first need to begin typing the function =SUBTOTAL.
Then, in the formula help section at the bottom left, you will see a “Learn More” button. Select that.
Please Note: If the formula help section is not visible, there will be a blue question mark box; click that or push F1 to open the formula help section.
Once you have selected the Learn More button, a right-hand sidebar will appear; in this you will see a detailed explanation of each function code. This may come in handy if you forget.
The function codes are as follows:
- 1 = AVERAGE
- 2 = COUNT
- 3 = COUNTA
- 4 = MAX
- 5 = MIN
- 6 = PRODUCT
- 7 = STDEV (Standard Deviation)
- 8 = STDEVP (Standard Deviation Population)
- 9 = SUM
- 10 = VAR (Variance)
- 11 = VARP (Variance Population)
As you can see, there are 11 functions built into the SUBTOTAL function in Google Sheets. Additionally, you can tell the function to ignore hidden cells by changing the function code. You use the same code, but you make it in the 100s. To run the functions while ignoring the hidden cells, one would use the below codes:
- 101 = AVERAGE
- 102 = COUNT
- 103 = COUNTA
- 104 = MAX
- 105 = MIN
- 106 = PRODUCT
- 107 = STDEV
- 108 = STDEVP
- 109 = SUM
- 110 = VAR
- 111 = VARP
Here is an example of the SUBTOTAL function being used to sum a range of data. As you will notice, the function code of 9 communicates that it should sum the range.
How to SUBTOTAL in Google Sheets
One may ask why you would use the SUBTOTAL function to run a SUM function? Wouldn’t it be more simple to use the SUM function? These questions are completely understandable and logical. However, situations exist that would make the SUBTOTAL function more valuable and effective.
Suppose you had the following dataset and you chose to use the SUM function in place of the SUBTOTAL function:
In the chart below, you will see that the SUM function is in each total cell and the grand total cell at the bottom of the table.
If you were to manually add up all of the totals, you would find that the Sum of the Average Projected Sales and Actual Sales does not match the totals listed. However, each Quarter Total is correct. Because the SUM function is adding all the numbers in the set range. As a result, the Quarterly totals are being added into the figures making this method ineffective and problematic.
To avoid that, we need to change all of the SUM functions to SUBTOTAL functions. The SUBTOTAL function does not add in the other subtotal functions; thus, you get the correct totals.
Google Sheets SUBTOTAL Function for Filtered or Hidden Data
Suppose we have a large data set that is categorized and set up to filter by month. We have total cells to review our reports data. The goal is for the Total Cells to adjust based on the filter. So if we are looking at January’s data, the total cells need to reflect that.
Also, if we hide a row, we want the data cells to reflect that as well. The first thing to note is that, as you will see in the example, when the SUM function is used, it does not adjust when the data is filtered or hidden. With the SUBTOTAL function, the opposite is true.
You will see the table in question at the bottom of the sheet.
Initially, everything appears the same with the exact results. However, if we filter the results by month, we will begin seeing the differences.
To do this, we select the green filter drop-down at the top of the Month column. Select the month in question and hit OK.
You will now see that the data has changed, and many rows are filtered.
You will also notice that the SUM cells in the table below are unchanged.
However, the Filtered cells that use the subtotal function are adjusted only to show unfiltered cells.
Additionally, if there is an outlier or row of data you would like to exclude, you can hide that row of data, and the SUBTOTAL function that removes hidden rows is now updated even further.
How to Use the SUBTOTAL Function to Create a Dynamic Report Function Selector
The most versatile method of using the SUBTOTAL function on Google Sheets is to create a Report function selector. Allowing you to change the metrics being seen in the report quickly and can be done by combining various functions and operations with the SUBTOTAL function. It is relatively simple and the steps are below.
- The first step in this is to create a function table for the SUBTOTAL functions available.
- We will use this list for a Data Validation (Drop-Down) and a VLOOKUP function in the following steps.
- Next, you will create a data validation that refers to this list in the cell you wish to make your selector drop-down.
- To do this, right-click the cell in question, find Data Validation, and click that.
- Now you will see the data validation box. Select a list from a range and enter the aggregation column range from the list we created in step 1.
- Now that you have created the data validation drop down, you should see an arrow on the cell that lets you double click and select your option.
- The remaining steps are all related to writing the functions to get the results we need.
- We will be nesting a VLOOKUP function within our SUBTOTAL function. Nesting is when you use multiple functions together to get the desired result. To do this, you will write the following in the cell.
- The VLOOKUP is looking at the table we created in step 1, in the range AH4:AI14. It is looking for the contents in the drop-down cell we created in E54. Once it finds the correct row, it looks to the second column of the range for the result. It then plugs that number into the SUBTOTAL function for the function code.
- Now create a cell for the Ignore Hidden Rows function and input the below. It is the same as above, except we added “100+” to the Vlookup section, which will cause it to use the ignore hidden cells function code.
- Your selector is now functional and ready to use. However, if you want to make it more advanced, you can create a checkbox to control the ignore hidden cells function.
- Then in the results cell, you will need to enter the below function. You are using the same functions as above but controlled by an IF statement that will look at the check box and then decide which results to show. Allowing the user to toggle between show or ignore these cells.
Google Sheets has many beneficial functions. One of these is the SUBTOTAL Function. It has a highly dynamic nature and can benefit any reporting system on Google Sheets. Hopefully this tutorial has shown you how to SUBTOTAL in Google Sheets.