Subtotal in Google Sheets: A Practical Guide for 2026
Learn how to use SUBTOTAL in Google Sheets to calculate sub totals that respect filters and hidden rows. This guide covers syntax, function_num options, real-world examples, pitfalls, and advanced variations.
Subtotal in Google Sheets is computed with the SUBTOTAL function, ideal for dashboards that reflect filters. It can perform sums, averages, counts, and more, while automatically excluding hidden rows when filters are applied. For a simple total, use =SUBTOTAL(9, B2:B100) to sum visible cells, and pair with FILTER for dynamic ranges.
What subtotal means in Google Sheets
Subtotal in Google Sheets is calculated using the SUBTOTAL function, which is purpose-built for sums, averages, counts, and other metrics that should respond to filters and hidden rows. This is especially important when building dashboards or reports where the viewer might apply a filter. According to How To Sheets, the right subtotal function helps you keep totals accurate and interpretable as data visibility changes.
=SUBTOTAL(9, B2:B100) // 9 = SUM, only visible rows are includedIn this section, we’ll cover how to choose the function_num, when to use SUBTOTAL versus SUM, and how to apply it to common data layouts.
Function_num mappings and basic usage
The SUBTOTAL function accepts a function_num that selects the operation. Common choices: 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, 4 for MAX, 5 for MIN, 6 for PRODUCT, 7 for STDEV, 8 for STDEVP, and 9 for SUM. The function works on a single range per call, so you can subtotal a column or a row. You can also combine with other functions to tailor your metric.
=SUBTOTAL(1, C2:C100) // average of visible cells
=SUBTOTAL(9, C2:C100) // sum of visible cellsIf you’re new to SUBTOTAL, start with SUM (9) and then experiment with other function_nums to match your reporting needs.
Subtotals with filters and hidden rows
One of the biggest advantages of SUBTOTAL is that it respects visible rows when you apply filters. This makes it ideal for dashboards where users toggle filters to examine subsets of data. The typical pattern is to place SUBTOTAL in a total row and point it at the data column. When you filter the data, the subtotal updates automatically.
=SUBTOTAL(9, FILTER(B2:B100, A2:A100="North"))This example sums only the rows where the region is North. If you remove the filter, the result reverts to the full data set. You can substitute 1 for AVERAGE or 2 for COUNT to get different aggregates while preserving visibility behavior.
Practical examples: budgeting and sales analysis
Imagine you’re tracking monthly sales in column B and a category in column A. You want a running subtotal that respects filters like region or product. Using SUBTOTAL, you can create a dynamic total in a summary cell and keep your source data intact. This approach is common in budgeting templates and sales dashboards.
=SUBTOTAL(9, B2:B13)
=SUBTOTAL(9, FILTER(B2:B13, A2:A13="Electronics"))Additionally, you can combine SUBTOTAL with conditional formatting to highlight rows contributing to the subtotal, or with a secondary column to show percentage of total for each item.
Pitfalls, quirks, and best practices
While SUBTOTAL is powerful, it’s easy to misuse. Avoid mixing SUBTOTAL with regular SUM in the same total row without clarity about visibility. Remember that function_num determines the operation, and some operations (like AVERAGE) can produce unexpected results if there are blank cells. Always test with a representative sample and document your chosen function_num.
=SUBTOTAL(9, D2:D100) // correct for sums
=SUBTOTAL(1, D2:D100) // average over visible dataFor more complex dashboards, consider AGGREGATE for additional ignore/visibility options.
Advanced variations and related tools
If you need broader control over what gets subtotaled, you can mix SUBTOTAL with other functions like FILTER, SORT, and ARRAYFORMULA. You can also use AGGREGATE to specify how to handle hidden rows differently. The key is to keep your total inputs stable and clearly documented in your sheet design. This makes audits and handoffs easier for teams.
=AGGREGATE(9, 5, B2:B100) // 5 ignores hidden rows when used with certain data layoutsSteps
Estimated time: 45-60 minutes
- 1
Prepare your data
Set up a simple data table with a numeric column to subtotal and a category column that you may filter on. Ensure there are no non-numeric values in the subtotal column, or you may need to clean data first.
Tip: Use data validation to ensure numeric entries in the subtotal column. - 2
Choose a SUBTOTAL function_num
Decide which operation you need: SUM (9) for totals, AVERAGE (1) for averages, etc. In many cases, SUM is the defaults when calculating quick totals after filtering.
Tip: Prefer 9 for visible-row totals when you expect hidden rows to be ignored. - 3
Apply SUBTOTAL to a range
Enter the formula to compute the subtotal for a range. For example, =SUBTOTAL(9, B2:B100) sums visible B values.
Tip: Place the formula in a dedicated total cell to avoid overwriting source data. - 4
Test with filters
Apply a filter and verify that the subtotal updates to reflect only visible rows. Compare with a plain SUM to see the difference.
Tip: Use a contrasting dataset to verify behavior. - 5
Experiment with other functions
Try AVERAGE, MAX, MIN, or COUNT to see how function_num changes the result while preserving visibility rules.
Tip: Keep a reference sheet mapping function_num to operations. - 6
Edge cases and validation
Test with merged cells, hidden rows, and non-numeric entries to understand how SUBTOTAL behaves. Document any anomalies for your team.
Tip: Always validate results with a manual check on a subset.
Prerequisites
Required
- Required
- Basic knowledge of Google Sheets formulasRequired
- Internet connectionRequired
Optional
- Sample dataset to practiceOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells | Ctrl+C |
| PastePaste into a target range | Ctrl+V |
| Fill downFill formulas down a column | Ctrl+D |
| UndoUndo last action | Ctrl+Z |
| RedoRedo last action | Ctrl+Y |
| FindFind in sheet | Ctrl+F |
FAQ
What is the main difference between SUBTOTAL and SUM in Google Sheets?
SUBTOTAL computes a subtotal while respecting data visibility (filters). SUM, by contrast, always sums values in the range regardless of filter state. Use SUBTOTAL to build dynamic reports that reflect user-filtered data.
SUBTOTAL only sums what you can see after filtering; SUM ignores filters.
Can SUBTOTAL ignore hidden rows when I manually hide rows?
SUBTOTAL's behavior depends on the way rows are hidden. In typical usage, SUBTOTAL ignores rows hidden by filters, and you should verify results when manually hiding rows.
It depends on how rows are hidden; test in a sample sheet.
What function_num should I use for average totals?
Use 1 for AVERAGE with SUBTOTAL. Other numbers map to MAX, MIN, COUNT, and SUM for different statistics. Always confirm the mapping in the current Sheets version.
Use 1 for average with SUBTOTAL.
Can I subtotal multiple columns at once?
SUBTOTAL operates on a single range per call. To subtotal multiple columns, call SUBTOTAL separately for each column or wrap with SUM to combine results.
Call SUBTOTAL for each column, or combine results.
What about using SUBTOTAL with FILTER?
You can nest FILTER inside SUBTOTAL to subtotal only the rows that match a condition. Example: =SUBTOTAL(9, FILTER(B2:B100, A2:A100="North"))
Nest FILTER inside SUBTOTAL to target specific rows.
The Essentials
- Use SUBTOTAL to respect visible data after filtering
- Choose the correct function_num for your metric
- Test across filters to validate results
- Combine with FILTER for dynamic dashboards