Try the example, then check the result.
Quick answer: Use =SUBTOTAL(9,C2:C7) to sum only rows visible after an automatic filter. Use 109 instead of 9 if rows you hide manually must also be excluded. Check the practice totals: all $540, East $400, West $140.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer: Use =SUBTOTAL(9,C2:C7) to sum only rows visible after an automatic filter. Use 109 instead of 9 if rows you hide manually must also be excluded. Check the practice totals: all $540, East $400, West $140.
Use SUBTOTAL to total only visible filtered rows in a fictional six-order Google Sheet, then test East, West, and manually hidden rows. Download the fictional practice and answer check. The optional CSV source may require separator settings in some locales; the .xlsx workbook is the primary exercise.
Before starting: Use the .xlsx practice workbook so IDs, regions, and numeric amounts stay in their intended columns regardless of CSV separator settings. The optional comma-separated CSV may need an explicit separator choice on import. Google says rows hidden by an automatic filter are excluded by SUBTOTAL with either 9 or 109. The 109 code additionally excludes manually hidden rows. A SUM across the original range remains $540 under either filter. Screen captures must come from a genuine English Sheets demo, not a drawn interface.
1. Start with six fictional orders
Open the practice Excel workbook in Google Sheets. It preserves three columns and numeric amounts across regional import settings. East orders are one hundred twenty, forty-five, two hundred, and thirty-five dollars. West orders are eighty and sixty dollars. Read those six values before adding formulas. Put calculation cells above or beside the data rather than inside the amount range, so the total never includes itself.
Checkpoint: 6 fictional rows; East: 120 + 45 + 200 + 35; West: 80 + 60.
2. Prove the unfiltered baseline
In an empty cell outside the table, enter equals SUM, open parenthesis, C two colon C seven, close parenthesis. It returns five hundred forty. That is the baseline across all six amounts. If you get a different value, inspect the imported amount cells: they must be numbers, not dollar signs embedded in text. The CSV uses plain numbers so the sheet can calculate them without cleaning.
Checkpoint: =SUM(C2:C7); Expected: 540; Check numeric cells.
3. Turn on a real filter
Select the header and all six data rows, then use Data, Create a filter. In the Region header, leave only East selected. You should now see four order rows and two West rows hidden by the filter. The SUM baseline still includes all six values, so it stays at five hundred forty. This mismatch is the symptom we are fixing, not a spreadsheet calculation failure. Select only A one through C seven for the filter. Keep every summary cell outside that selection, or the filter may hide the answer at the same time as a data row. Read the four visible East order IDs before trusting the displayed total.
Checkpoint: Data > Create a filter; Choose East; SUM still includes West.
4. Add a visible-row total
In the next empty cell, type exactly equals SUBTOTAL, open parenthesis, nine, comma, C two colon C seven, close parenthesis. Function code nine means SUM. In Google Sheets, rows hidden by an automatic filter are excluded from SUBTOTAL. With East selected, this formula returns four hundred. If you see five hundred forty, confirm that an actual sheet filter is active and that the formula points to C two through C seven. Put this formula in an empty cell such as E three, not below the table inside the amount column. The fixed range covers this six-row exercise. If you later append orders, extend the range and keep the subtotal cell out of its own calculation.
Checkpoint: =SUBTOTAL(9,C2:C7); Code 9 means SUM; East answer: 400.
5. Check every visible contribution
Read the four visible East IDs, not just the formula result: H one-oh-one is one hundred twenty; H one-oh-three is forty-five; H one-oh-four is two hundred; H one-oh-six is thirty-five. Adding them produces four hundred. This row-by-row reconciliation catches a wrong filter, an accidentally included blank row, or a changed amount. The guide provides the exact fictional answer list for comparison.
Checkpoint: 120 + 45 + 200 + 35; Four East rows; Answer: 400.
6. Switch the filter to West
Open the Region filter again and select West only. Now the visible rows are H one-oh-two for eighty dollars and H one-oh-five for sixty. The SUBTOTAL formula changes to one hundred forty, while SUM remains five hundred forty. This second filter proves the formula responds to visibility rather than being a typed-in East total. Clear the filter afterward to return to all six rows.
Checkpoint: West: 80 + 60; SUBTOTAL: 140; SUM: 540.
7. Understand code 9 versus 109
One more case matters. Code nine always ignores rows hidden by an automatic filter, but it may include a row hidden manually. Code one-oh-nine also excludes manually hidden rows. Restore the East filter, hide the H one-oh-three row manually, and compare the two formulas. The first remains four hundred; the second becomes three hundred fifty-five, because forty-five is removed. This is a separate visibility rule, not a different aggregation. Use the row header's Hide row action for the forty-five-dollar order while the East filter remains selected. Do not remove East from the filter or change the region criteria; the point is to compare automatic filtering with an additional manual hide on the same four matching records.
Checkpoint: 9: filtered rows excluded; 109: manual hide excluded too; East without $45: $355.
8. Count the visible order IDs
Use an independent second formula to check that the sum matches the number of rows you think you are viewing. In an empty summary cell, enter equals SUBTOTAL, open parenthesis, one-oh-three, comma, A two colon A seven, close parenthesis. Code one-oh-three counts nonblank visible order IDs and ignores manually hidden rows too. East shows four before the manual hide, West shows two, and East shows three after you hide H one-oh-three. A count mismatch means you should inspect the filter or hidden rows before trusting the dollar total. Every fictional order ID is filled, so COUNTA serves as a reliable row count here. If your real list permits blank IDs, use a different column that is guaranteed populated. A matching visible count protects you from celebrating the right dollar answer for the wrong subset of orders.
Checkpoint: =SUBTOTAL(103,A2:A7); East: 4; West: 2; Manual-hide East: 3.
9. Choose the total you actually need
For a dashboard that responds only to a region filter, code nine is enough, and it will still include any manually hidden row within the selected region. If a teammate manually hides rows to exclude them from today's working view, code one-oh-nine is the safer visible-row total. Keep the code choice visible in the sheet notes so someone reading four hundred versus three hundred fifty-five understands why. Neither formula changes source amounts; both recalculate from the same six fictional cells. Choose deliberately. Code one-oh-nine is often easier to explain as a total of everything actually visible. Code nine is appropriate when manual hiding is only a personal viewing convenience and those orders should still count. The filter-only East and West answers match under both codes; the manual-hide case reveals the difference.
Checkpoint: 9: filter-aware; 109: also manual-hide-aware; Label the summary.
10. Fix the common wrong result
If both SUBTOTAL cells show five hundred forty under the East filter, check the filter icon and whether you hid rows by a different method. If a value is blank or stored as text, reconcile C two through C seven against the CSV before changing the formula. If you are making a dashboard, keep the calculation cell outside the filtered data block. Do not replace a bad result with a hard-coded four hundred. If the formula appears literally as text instead of calculating, inspect whether the cell is formatted as plain text or starts with an apostrophe. Fix that separate input problem first. An incorrect value should be investigated in the sheet, never patched by typing the expected dollar amount over the formula.
Checkpoint: Verify filter state; Verify numeric range; Keep formula outside table.
Check the finished result
The final check has three states. With no filter, the six orders total five hundred forty. East yields four hundred, and West yields one hundred forty in SUBTOTAL. Under the East filter, manually hiding the forty-five-dollar row makes code one-oh-nine yield three hundred fifty-five, while code nine still yields four hundred. Unhide the row and clear the filter when finished. You now have a reusable visible-row total and know what each code actually excludes.
Expected answer: All six orders total $540. Filtering Region to East leaves H-101 $120, H-103 $45, H-104 $200, and H-106 $35, so SUBTOTAL(9,C2:C7) returns $400 and SUBTOTAL(103,A2:A7) counts 4 visible order IDs. Filtering to West leaves H-102 $80 and H-105 $60, returning $140 and a count of 2. Manually hiding East row H-103 changes SUBTOTAL(109,C2:C7) to $355 but leaves SUBTOTAL(9,C2:C7) at $400 while the East filter is active. Code 103 counts 3 visible IDs after that manual hide. Restore every hidden row before comparing the two filter results.
If it still fails
If SUBTOTAL stays at 540 under East, confirm that the Region filter actually hides the two West rows and that C2:C7 contains numeric amounts. Compare code 9 with 109 only after manually hiding H-103.
Official references
Checked 16 October 2026. On-screen explanatory panels are labelled illustrative diagrams; any separately identified actual capture is a genuine English app view.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.