Google Sheets SUBTOTAL Function: Unbelievably Easy Guide
SUBTOTAL is a versatile function in Google Sheets that can revolutionize your spreadsheet experience. With the right knowledge and techniques, you can unlock its full potential to enhance the functionality of your sheets. In this guide, we will explore the various ways you can use the SUBTOTAL function and its applications in reporting.
The 3 Primary Ways to Use the SUBTOTAL Function
- Applying essential functions (e.g., Sum, Count, Average) to a list of data.
- Creating a reporting selector to view different statistics from one set of data.
- Calculating data with or without hidden rows.
These applications may seem intimidating at first, especially if you are new to using the SUBTOTAL function. However, fear not! There are ways to harness its power without having to memorize the function code list. If you feel overwhelmed, consider taking a Google Sheets course to gain a deeper understanding of this powerful program.
Now, let’s dive into how to do subtotals in Google Sheets with practical examples. By the end of this tutorial, you will have a solid grasp of the versatility and simplicity of using subtotals in Google Sheets.
Download a Copy of Our Example Spreadsheet
To fully follow along with this guide, you can download a copy of our Example Sheet. If you find the example sheet useful, you may also want to explore our paid templates. For a limited time, you can save 50% at our Gumroad store by using the code SSP.
Google Sheets SUBTOTAL Function: Syntax
Let’s start with the syntax for the SUBTOTAL function 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, available in Google Sheets at any time, are pictured below. To access them, begin typing the function =SUBTOTAL. In the formula help section at the bottom left, click on “Learn More.”
Please Note: If the formula help section is not visible, look for a blue question mark box or press F1 to open it.
Function Code
Once you have clicked on “Learn More,” a right-hand sidebar will appear, providing a detailed explanation of each function code. This can be useful if you ever forget.
The Google Sheets subtotal function codes include:
- AVERAGE
- COUNT
- COUNTA
- MAX
- MIN
- PRODUCT
- STDEV (Standard Deviation)
- STDEVP (Standard Deviation Population)
- SUM
- VAR (Variance)
- VARP (Variance Population)
There are 11 functions built into the SUBTOTAL function in Google Sheets. Additionally, you can tell the function to ignore hidden cells by using the same function codes but in the 100s range. For example, 101 = AVERAGE, 102 = COUNT, and so on.
Here’s an example of using the SUBTOTAL formula to sum a range of data with the function code 9: =SUBTOTAL(9, A1:A10)
How to SUBTOTAL in Google Sheets
You might wonder why you would use the SUBTOTAL function in Google Sheets to run a SUM function when the SUM function itself seems simpler. That’s a valid question! However, there are situations where the SUBTOTAL function proves more valuable and effective, such as when adding subtotals in Google Sheets.
Let’s consider the following dataset and assume you chose to use the SUM function instead of the SUBTOTAL function:
In the chart below, you see that the SUM function is used in each total cell as well as the grand total cell at the bottom of the table:
If you were to manually add up all 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. This discrepancy arises because the SUM function adds all the numbers in the set range, which includes the quarterly totals, making this method ineffective and problematic.
To avoid this issue, we need to change all the SUM functions to SUBTOTAL functions. The SUBTOTAL function does not include the other subtotal functions, thus providing the correct totals. The example below demonstrates how to create subtotals in Google Sheets:
Google Sheets SUBTOTAL Function for Filtered or Hidden Data
Suppose you have a large dataset categorized and set up to filter by month. You have total cells to review your report’s data. The goal is for the total cells to adjust based on the filter. For example, if you are looking at January’s data, the total cells should reflect that.
When using the SUM function, it does not adjust when the data is filtered or hidden. However, with the SUBTOTAL function, the opposite is true.
In the example above, you can see that the total at the bottom of the sheet remains the same even when some months are hidden.
To achieve this:
Step 1: Select the green filter drop-down at the top of the Month column.
Step 2: Choose the month you want to view and click OK. You will now see that the data has changed, with many rows filtered.
Step 3: Enter the SUBTOTAL formula in the cell for the total.
You will also notice that the SUM cells in the table below remain unchanged. However, the filtered cells that use the SUBTOTAL function adjust to show only the unfiltered cells.
Additionally, if you want to exclude an outlier or row of data, you can hide that row, and the SUBTOTAL function that removes hidden rows will update accordingly.
How to Use the SUBTOTAL Function to Create a Dynamic Report Function Selector
The most versatile application of the SUBTOTAL function in Google Sheets is to create a dynamic report function selector. This allows you to quickly change the metrics displayed in the report. You can achieve this by combining various functions and operations with the SUBTOTAL function. Let’s explore how to do this using the Google Sheets subtotal with conditions.
In our example sheet, we can create a dynamic report that displays information such as the average, sum, max, and min for the Actual Sale column:
The process is relatively simple, and the steps are as follows:
Step 1: Create a function table for the available SUBTOTAL functions.
Step 2: Create a data validation (drop-down) that refers to this function table. Right-click the cell and go to Data > Data Validation.
Step 3: Click Add rule and choose “Dropdown (from a range).” Select the aggregation column range from the list created in step 1 and click OK.
Now, you have a data validation drop-down menu that allows you to select your desired option by double-clicking the arrow on the cell.
To get the results you need, follow these remaining steps:
Step 5: Nest a VLOOKUP function within your SUBTOTAL function. This is achieved by writing the following in the cell: =SUBTOTAL(VLOOKUP(G2, J1:K9, 2, FALSE), D:D)
The VLOOKUP function looks at the table created in step 1, searches for the contents in the drop-down cell created in E54, and returns the result from the second column of the range. This result is then passed into the SUBTOTAL function as the function code.
Step 6: Create a cell for the Ignore Hidden Rows function and input the following formula: =SUBTOTAL(100 + VLOOKUP(G2, J1:K9, 2, FALSE), D:D)
This formula is the same as the previous one, but it includes “100+” in the VLOOKUP section. This causes the function to use the ignore hidden cells function code.
Now, your selector is functional and ready to use. To make it more advanced, you can create a checkbox to control the ignore hidden cells function. Additionally, you can use an IF statement to allow the user to toggle between showing or ignoring these cells in the results.
Frequently Asked Questions
Why Use SUBTOTAL Instead of SUM?
Unlike the SUM function, SUBTOTAL can be used repeatedly in the same column to add a subtotal by category without affecting the overall calculation. It can also exclude filtered values from the calculations, making it more flexible than the SUM function.
Is SUBTOTAL the Same as Total?
A subtotal represents the total of a specific set of data in a range, while “total” refers to the sum of all the data in the range. The SUBTOTAL function in Google Sheets can accurately calculate the sum of subsets in the data as well as the total of the entire data at the same time.
Conclusion
Google Sheets offers a multitude of useful functions, and the SUBTOTAL function is no exception. Its dynamic nature can greatly enhance any reporting system in Google Sheets. This tutorial has provided you with the necessary knowledge to master the SUBTOTAL function in Google Sheets.
For more Google Sheets tips and tricks, be sure to check out our guide on how to add a calculated field in Google Sheets.
Related:
- How to Multiply in Google Sheets (Numbers, Cells or Columns)
- Easy Guide: How to Subtotal in Google Sheets
- How to Divide in Google Sheets (Numbers, Cells, or Columns)
- How to Merge Cells in Google Sheets
- How to Apply a Formula to an Entire Column in Google Sheets
- Slow Google Sheets? Easy Ways to Speed Up
- How to Compare Two Columns in Google Sheets
- How to Use the SUMIF function in Google Sheets? Examples!
