This guide covers everything you need to know about the Google Sheets SUBTOTAL function, including its definition, syntax, use cases, and how to use it.
What Is the SUBTOTAL Function? How Does It Work?
The SUBTOTAL function in Google Sheets helps you do calculations like adding up numbers, finding the average, or identifying the smallest or largest number in a column of data. It’s a tool that gives you a single answer, like a total or an average, based on the numbers you’ve got in a specific column.
For instance, if you have a sales report with data from different regions, you can use the SUBTOTAL function to calculate the total sales for each region separately.
One of the unique characteristics of the SUBTOTAL function is that it automatically excludes any hidden cells due to auto-filter criteria. This feature is especially useful when you want to analyze certain data subsets without manually excluding the hidden cells.
The SUBTOTAL function also prevents double-counting. Any cells within the specified range that contain SUBTOTAL calls are automatically ignored. This feature ensures accuracy in calculations when the function is used multiple times within a range.
The SUBTOTAL function also allows for the creation of dynamic dashboards. By referring the function code argument to another cell, you can create a drop-down list that instantly updates the entire dashboard.
SUBTOTAL Syntax
The syntax and arguments for the function are as follows:
SUBTOTAL(function_code, range1, [range2, …])
The arguments for the SUBTOTAL function are:
- function_code: This is a required argument determining the function to use in subtotal aggregation. The function_code can be any number from 1 to 11 or 101 to 111. Each number corresponds to a specific function:
- 1 is AVERAGE (Calculates the average of the specified range)
- 2 is COUNT (Counts the number of cells with numeric values in the range)
- 3 is COUNTA (Counts the number of non-empty cells in the range)
- 4 is MAX (Returns the highest value in the range)
- 5 is MIN (Returns the lowest value in the range)
- 6 is PRODUCT (Multiplies all the numbers in the range)
- 7 is STDEV (Estimates the standard deviation based on a sample)
- 8 is STDEVP (Calculates the standard deviation based on the entire population)
- 9 is SUM (Adds all the numbers in the range)
- 10 is VAR (Estimates variance based on a sample)
- 11 is VARP (Calculates variance based on the entire population)
- The codes 1 to 11 include all cells in the range. However, if you wish to exclude hidden rows while calculating, use the codes 101 to 111. For instance, 102 would be for COUNT while skipping hidden cells, and 110 would be for VAR while doing so.
- range1: This is also a required argument. It specifies the first range over which to calculate a subtotal.
- range2, …: These are optional arguments. They represent additional ranges over which to calculate subtotals.
Examples of How to Use the SUBTOTAL Function
Here are some practical examples of how to use the SUBTOTAL function in Google Sheets:
Example #1: Calculating the sum of a range of cells
Let’s say you have a column of numbers in cells A1 to A10, and you want to calculate the sum of these numbers. You can use the SUBTOTAL function for this purpose. The formula would be:
=SUBTOTAL(9, A1:A10)
The number 9 in the formula represents the function number for SUM. This will give you the sum of all the numbers in cells A1 to A10.
Example #2: Calculating the average of a range of cells
If you want to calculate the average numbers in cells A1 to A10, use the SUBTOTAL function with the function number for AVERAGE, which is 1. The formula would be:
=SUBTOTAL(1, A1:A10)
This will give you the average of all the numbers in cells A1 to A10.
Example #3: Counting the number of cells in a range
The SUBTOTAL function can also be used to count the number of cells in a range that contains numbers. For example, if you want to count the number of cells in A1 to A10 that contain numbers, you would use the SUBTOTAL function with the function number for COUNT, which is 2. The formula would be:
=SUBTOTAL(2, A1:A10)
This will give you the count of cells in A1 to A10 that contain numbers.
Example #4: Finding the maximum value in a range
To find the maximum value in a range of cells, you can use the SUBTOTAL function with the function number for MAX, which is 4. For example, if you want to find the maximum value in cells A1 to A10, the formula would be:
=SUBTOTAL(4, A1:A10)
This will give you the maximum value in cells A1 to A10.
Example #5: Finding the minimum value in a range
Similarly, to find the minimum value in a range of cells, you can use the SUBTOTAL function with the function number for MIN, which is 5. For example, if you want to find the minimum value in cells A1 to A10, the formula would be:
=SUBTOTAL(5, A1:A10)
This will give you the minimum value in cells A1 to A10.
Why Is SUBTOTAL Not Working? Troubleshooting Common Errors
If you are using the SUBTOTAL function in Google Sheets and it seems not to be working as expected, you might encounter one of the common errors. Here’s a guide to help you troubleshoot these issues.
#VALUE! Error
Cause: The #VALUE! error usually occurs when the function or formula you’ve entered in the Google Sheets cell has an incorrect argument. In the case of the SUBTOTAL function, this error can occur if you’ve entered a non-numerical value as an argument or selected an invalid function number.
Solution: Check the arguments in your SUBTOTAL function. Ensure that the function number you’ve selected is between 1-11 or 101-111, as these are the only valid numbers for the SUBTOTAL function. Also, ensure that the range you’ve selected for the SUBTOTAL function only contains numerical values.
#REF! Error
Cause: The #REF! error is displayed when a formula references a cell that doesn’t exist. This often happens when a cell that the formula depends on was deleted, or pasting a range of cells over cells that are being referenced by a formula.
Solution: To fix this, make sure that the cell references in your SUBTOTAL function are correct and that none of the referenced cells have been deleted or overwritten.
#NUM! Error
Cause: The #NUM! error happens when a formula or function contains invalid numeric values. For the SUBTOTAL function, this error occurs if your function number argument is less than 1 or greater than 111.
Solution: The function number argument in the SUBTOTAL function should be between 1-11 or 101-111. Make sure that your function number falls within these ranges.
#DIV/0! Error
Cause: The #DIV/0! error is displayed when a number is divided by zero. In the SUBTOTAL function, this error can occur if you use a function number that performs division (like AVERAGE, VAR, or STDEV) and the range of cells you’ve selected contains zero or no numerical values.
Solution: If you’re using a function number that performs division, ensure that your selected range contains numerical values and does not include zero as the only value. If you want to include zero in your range, ensure there are other numerical values as well.
#N/A Error
Cause: The #N/A error is shown when a value is not available to a function or formula. In the SUBTOTAL function, this could happen if your selected range does not contain any cells or if the function number you’ve selected does not apply to the range.
Solution: Ensure that your selected range contains at least one cell and that the function number is appropriate for the data in your range. For example, if you’re using the AVERAGE function number, your range should contain numerical values.
For more details on the SUBTOTAL function, check out the official documentation at the Google Docs Editors Help Center.
