# Google Sheets SUBTOTAL Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/google-sheets/subtotal-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) The SUBTOTAL function calculates a specified aggregate (like SUM or AVERAGE) for a range, allowing for filtered data to be included or excluded. ``` SUBTOTAL(function_code, range1, [range2, ...]) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the SUBTOTAL you need in plain EnglishGenerate Formula ## Parameters **`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: | SaleID | ProductName | Quantity | Price | | - | - | - | - | | 1 | Widget A | 3 | 25.00 | | 2 | Widget B | 5 | 30.00 | | 3 | Widget C | 2 | 15.00 | | 4 | Widget A | 4 | 25.00 | | 5 | Widget B | 1 | 30.00 | 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: | ProductName | Total Sales | | - | - | | Widget A | 175.00 | If you were to apply a filter to show only Widget B, the result would change accordingly: | ProductName | Total Sales | | - | - | | Widget B | 60.00 | ## 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! ## Example use case You have a sales data table with monthly sales figures in Column B. Using the SUBTOTAL formula, you can calculate the sum of visible sales data after applying filters, allowing for dynamic analysis of sales performance without affecting hidden rows. ## Need more help with Google Sheets formulas? Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Google Sheets how-to guides - [How To Wrap Text In Google Sheets](https://betteranalyst.com/google-sheets/how-to-wrap-text-in-google-sheets) - [Google Sheets Conditional Formatting](https://betteranalyst.com/google-sheets/google-sheets-conditional-formatting) - [How To Add A Column In Google Sheets](https://betteranalyst.com/google-sheets/how-to-add-a-column-in-google-sheets) - [How To Alphabetize In Google Sheets](https://betteranalyst.com/google-sheets/how-to-alphabetize-in-google-sheets) - [How To Autofit In Google Sheets](https://betteranalyst.com/google-sheets/how-to-autofit-in-google-sheets) - [How To Calculate Percentage In Google Sheets](https://betteranalyst.com/google-sheets/how-to-calculate-percentage-in-google-sheets) - [How To Combine Two Columns In Google Sheets](https://betteranalyst.com/google-sheets/how-to-combine-two-columns-in-google-sheets) - [How To Copy Formula In Google Sheets](https://betteranalyst.com/google-sheets/how-to-copy-formula-in-google-sheets) ## Explore all Google Sheets formulas Browse our complete library of Google Sheets formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.