Google Sheets Subtotal 9: A Practical Guide to Summing Visible Data
A comprehensive guide to google sheets subtotal 9, covering syntax, practical examples, and tips to sum visible data after filtering. Learn when to use SUBTOTAL(9) vs SUM, how to handle hidden rows, and build robust reports in Google Sheets with real-world templates.
SUBTOTAL with function_num 9 in Google Sheets sums a range while respecting filters and any rows hidden by a filter. It’s ideal for dashboards and reports because it sums only visible data when you filter. Use SUBTOTAL(9, range) for a basic sum, and SUBTOTAL(109, range) to explicitly ignore manually hidden rows. This quick guide covers syntax and practical examples for google sheets subtotal 9.
Quick refresher: what SUBTOTAL(9) does in Google Sheets
SUBTOTAL(9) is the SUM variant in Google Sheets that respects filters. It sums only the rows that are visible after applying a filter, which makes it ideal for dashboards and live reports. This behavior is what sets it apart from a plain SUM that counts every value regardless of visibility. In practice, you’ll often pair SUBTOTAL(9) with a filtered data view to present a clean subtotal on a dashboard.
=SUBTOTAL(9, B2:B20)- This formula sums B2 through B20 and includes only rows not hidden by an active filter. If you filter the data, the subtotal updates accordingly.
=SUBTOTAL(109, B2:B20)- The 109 variant ignores hidden rows even if they aren’t part of a formal filter, which can be useful when you manually hide rows for a quick view. The behavior can vary slightly by sheet settings, so test with your data.
According to How To Sheets, mastering SUBTOTAL(9) unlocks more accurate analyses when filters are applied, helping you maintain reliable dashboards across dynamic datasets.
tipOnlyForSection_nullization":null
Steps
Estimated time: 20-40 minutes
- 1
Identify data range
Scan your sheet to locate the numeric column you want to subtotal and identify any filters that will affect visibility. Decide whether you’ll sum a single contiguous range (e.g., B2:B100) or a named range.
Tip: Use a named range (e.g., Amounts) for easier maintenance in larger sheets. - 2
Insert SUBTOTAL for a basic sum
Enter the SUBTOTAL formula in a blank cell to sum the target range. Start with a simple example to verify behavior before adding complexity.
Tip: Always reference a stable range to avoid accidental shifts when data grows. - 3
Test with a filter
Apply a filter to your data and confirm that SUBTOTAL(9, range) updates to reflect only visible rows. Compare with SUM to see the difference.
Tip: If you don’t see updates, recheck the range and filter scope. - 4
Try the 109 variant to ignore hidden rows
If you need to ignore rows manually hidden (not just filtered), test SUBTOTAL(109, range) and compare results with the 9 variant.
Tip: 109 ignores hidden rows, which is useful for reproducible reporting. - 5
Conditional sums with FILTER
When you need conditional sums, combine SUBTOTAL with FILTER to sum only rows that meet a condition.
Tip: Use FILTER to create a dynamic subrange for SUBTOTAL. - 6
Validate results and document
Cross-check with a small sample dataset and document assumptions (e.g., which rows are hidden by filters).
Tip: Add a short note in the sheet explaining SUBTOTAL usage for future users.
Prerequisites
Required
- Required
- A dataset in Google Sheets containing numeric valuesRequired
- Basic knowledge of spreadsheet formulasRequired
- A modern web browser (Chrome, Edge, Firefox)Required
Optional
- Optional: familiarity with FILTER and named ranges for advanced useOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaCopy a subtotal formula from one cell to another | Ctrl+C |
| Paste formulaPaste the copied formula into target cells | Ctrl+V |
| Fill downCopy the formula to adjacent rows in the same column | Ctrl+D |
| UndoUndo the last formula edit | Ctrl+Z |
FAQ
What is the Google Sheets subtotal 9 function and when should I use it?
SUBTOTAL(9) is the SUM variant of the SUBTOTAL family that respects filters, summing only visible rows. It’s ideal for dashboards and reports where data is frequently filtered. Use it when you need to reflect current filter state in your totals.
SUBTOTAL 9 sums only what's visible after filtering, which keeps your totals accurate in dashboards.
How does SUBTOTAL differ from SUM in Google Sheets?
SUM adds every value in the specified range, regardless of visibility. SUBTOTAL with function_num 9 respects filters and hidden rows, giving you sums that reflect the current view. For ignoring manually hidden rows, use SUBTOTAL(109, range).
SUBTOTAL can ignore hidden rows, unlike SUM, depending on the function number you choose.
Can I sum across multiple non-contiguous ranges with SUBTOTAL?
SUBTOTAL operates on a single range per function call. To sum multiple non-contiguous ranges, you can use multiple SUBTOTAL calls and add them together, or use a helper column to consolidate the ranges.
SUBTOTAL handles one range at a time; for multiple ranges, sum the results of several subtotals.
What is the advantage of using 109 over 9?
109 is the variant of SUBTOTAL that ignores manually hidden rows, making it more suitable when you manually hide rows for analysis. Use 9 for general cases and 109 when you want to ensure hidden manual rows don’t affect totals.
109 ignores hidden rows, great for manual hiding scenarios.
How can I sum only visible data after applying a condition?
Use SUBTOTAL with FILTER, for example: =SUBTOTAL(9, FILTER(B2:B20, A2:A20="Sales")). This sums only rows where the condition is met and are visible after filtering.
Filter then subtotal to sum only the matched and visible data.
The Essentials
- Sum only visible data with SUBTOTAL(9)
- Use 109 to ignore hidden rows
- Combine with FILTER for conditional sums
- Prefer named ranges for dynamic data
- Test across filters to ensure correct results