Check Sheet Logo
Check Sheet

SUBTOTAL

The 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.

Usage

Use the SUBTOTAL formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=SUBTOTAL(function_code, range1, [range2, ...])
Parameters:
  1. 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.
  2. range1 (required):
    The first range of cells to include in the subtotal calculation.
  3. range2, ... (optional):
    Optional additional ranges of cells to include in the subtotal calculation.

Examples

Here 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.

Common Mistakes

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.

Related Formulas

The following functions are similar to SUBTOTAL or are often used with it in a formula:

  • SUM

    The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

  • AVERAGE

    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.

  • COUNT

    The COUNT formula 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.

  • MAX

    The MAX function 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.

  • MIN

    The MIN formula 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.

Learn More

You can learn more about the SUBTOTAL Google Sheets function on Google Support.