SheetFX
≡
<< Go back to all functions

SUBTOTAL

The SUBTOTAL function in Google Sheets is a powerful tool to calculate subtotals for a vertical range of cells using specified aggregation functions. Whether you need to calculate sums, averages, counts, or other types of aggregations, the SUBTOTAL function simplifies the task. Dive into our comprehensive guide to master its application.

Function Syntax and Parameters

Syntax: SUBTOTAL(function_code, range1, [range2, ...])

Parameters:

  • function_code: A number that specifies the aggregation function to be used. Here are some commonly used function codes:
    • 1: AVERAGE
    • 2: COUNT
    • 3: COUNTA
    • 4: MAX
    • 5: MIN
    • 6: PRODUCT
    • 7: STDEV
    • 8: STDEVP
    • 9: SUM
    • 10: VAR
    • 11: VARP
  • range1: The first column or range to consider for calculations.
  • range2, ...: [Optional] Additional columns or ranges to consider.

Step-by-Step Tutorial

  1. Using SUBTOTAL with different aggregation functions:

    • Example 1: To calculate the sum of numbers in the range A1:A10, use =SUBTOTAL(9, A1:A10).
    • Example 2: To count the non-blank cells in the range C1:C5, use =SUBTOTAL(3, C1:C5).

  2. Using SUBTOTAL with multiple ranges:

    • Example: If you want to calculate the average and sum of numbers in both the ranges B1:B5 and D1:D5, you can use =SUBTOTAL(1, B1:B5, D1:D5) and =SUBTOTAL(9, B1:B5, D1:D5) respectively.

Use Cases and Scenarios

  1. Financial Analysis: Calculate the total sales, average expenses, or other financial metrics for a set of transactions.
  2. Data Analysis: Obtain various statistics such as count, sum, or average on a subset of data within a larger dataset.
  3. Inventory Management: Determine the total quantity, minimum stock level, or other inventory-related calculations.

Related Functions

  • SUM: Calculate the sum of a range of cells.
  • COUNT: Count the number of cells that contain numbers within a range.
  • AVERAGE: Calculate the average of a range of cells.
  • MAX: Find the largest value in a set.
  • MIN: Find the smallest value in a set.

Related Articles