SUBTOTAL formula is a mathematical formula used to calculate a subtotal for a range of cells. It can perform different mathematical operations based on the function code specified as the first parameter. This formula is commonly used to calculate subtotals for a filtered range of cells.
- How to use
- Examples of using
SUBTOTALformula not working?
- Similar formulas to
SUBTOTAL formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- function_code (required):
A numerical value that specifies the mathematical operation to perform on the supplied range of cells. The function code can range from 1 to 11 or 101 to 111, depending on the operation you want to perform.
- range1 (required):
The first range of cells to include in the subtotal calculation.
- range2, ... (optional):
Optional additional ranges of cells to include in the subtotal calculation.
ExamplesHere are a few example use cases that explain how to use the
SUBTOTAL formula in Google Sheets.
Calculating a filtered subtotal
When a range of cells is filtered, you can use the
SUBTOTAL formula to calculate the subtotal for the visible cells.
Calculating a subtotal across multiple sheets
You can use the
SUBTOTAL formula across multiple sheets to calculate a subtotal for a range of cells in each sheet.
Calculating a conditional subtotal
You can use the
SUBTOTAL formula with a conditional statement to calculate a subtotal for cells that meet specific criteria.
SUBTOTAL not working? Here are some common mistakes people make when using the
SUBTOTAL Google Sheets Formula:
Incorrect function code
Users may enter an invalid function code, leading to incorrect calculation. Double-check the function code and refer to the documentation to ensure the correct code is used.
Not selecting a range for function calculation
Users need to select a range for the function to calculate. Ensure that at least one range is selected and that it contains the desired values.
Using non-numeric values in the specified range
If the specified range contains non-numeric values, the function will return an error. Ensure that only numeric values are included in the range.
Specifying overlapping ranges
If overlapping ranges are specified, the function may return incorrect results. Ensure that the specified ranges do not overlap.
Using hidden or filtered cells in the specified range
If the specified range contains hidden or filtered cells, the function may return incorrect results. Ensure that all cells in the specified range are visible and unfiltered.
The following functions are similar to
SUBTOTAL or are often used with it in a formula:
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.
COUNTformula returns the number of numeric values within a specified range of cells. It is commonly used to count the number of entries in a given range that meet specific criteria, such as counting the number of sales above a certain threshold.
MAXfunction returns the maximum value from a range of cells or a set of supplied values. It is commonly used to find the largest value in a dataset.
MINformula is a statistical function that returns the smallest value in a set of numbers. It is commonly used to find the minimum value from a range of cells or a list of values.
You can learn more about the
SUBTOTAL Google Sheets function on Google Support.