Google Sheets SUBTOTAL Function: Syntax, Examples & Tips

function_code:
A number that specifies which function to use for the subtotal calculation. It determines the type of aggregation (e.g., SUM, AVERAGE, COUNT) and whether to include hidden rows.

range1:
The first range of cells that you want to include in the subtotal calculation.

range2:
An optional additional range of cells that you want to include in the subtotal calculation. You can include multiple ranges as needed.

What is a SUBTOTAL?

The SUBTOTAL function in Google Sheets is a versatile tool used to perform calculations on a range of data while allowing for filtering and excluding hidden rows. It can be used for various operations such as SUM, AVERAGE, COUNT, and more, making it an essential function for data analysis and reporting.

In simpler terms, SUBTOTAL allows you to calculate summary statistics for a dataset while taking into account any filters applied to that dataset.

A Practical Example

Imagine you are managing a sales report stored in a Google Sheets document:

Sales Data Table:

You want to calculate the total sales amount while being able to filter the data by product.

SUBTOTAL Formula

To achieve this, you would use the SUBTOTAL function as follows:

=SUBTOTAL(109, D2:D6)

In this formula:

• 109 is the function number that specifies the operation (in this case, SUM while ignoring hidden rows).
• D2:D6 is the range of cells containing the Price values that you want to sum.

Result of the Formula

When you apply the SUBTOTAL formula to the Sales Data Table, the output would show the total sales amount. If you filter the table to show only Widget A, the SUBTOTAL will automatically adjust to reflect only the visible rows:

If you were to apply a filter to show only Widget B, the result would change accordingly:

Why Use SUBTOTAL?

SUBTOTAL is beneficial because it allows for dynamic calculations that respond to filtering, making it ideal for analyzing large datasets. It simplifies the process of summarizing data without needing to adjust formulas manually when rows are hidden or filtered out.

Key Takeaways:

• SUBTOTAL: A powerful function that performs calculations on a range while considering filtered data.
• Versatile: Supports various operations such as SUM, AVERAGE, COUNT, and more.
• Dynamic: Automatically adjusts calculations based on visible rows, enhancing data analysis.
• Common Use Cases: Ideal for sales reports, financial summaries, and any scenario where filtered data analysis is necessary.

SUBTOTAL is an essential function for anyone working with data in Google Sheets, providing a robust way to enhance data analysis and reporting capabilities.

Happy spreadsheeting!