Excel Total Doesn't Change After Filtering? Comparing SUBTOTAL 9 and 109

VBA・Officeカテゴリを表すパンダのイラスト VBA / Office

About This ArticleThis article is created using an automated generation workflow leveraging generative AI. We reviewed the Microsoft Support description of the SUBTOTAL function and organized the conditions under which totals change based on filters and manual hiding.

Verification Status: 📘 Official Specifications Confirmed – Excel Physical Device Unverified

Supported Environments, Capabilities, and Limitations (Verified: November 17, 2026)

ItemTarget / Condition
Target ProductsExcel for Microsoft 365 (Windows), Excel 2024 and 2021
Operation LocationNew workbook in desktop Excel, data filtering, and row hiding
License / PermissionsEnvironment where Excel is available. Copilot, administrator privileges, and external services are not required.
Save LocationPractice workbook on local PC. Uploading to OneDrive or SharePoint is not required.
CapabilitiesCalculate totals while distinguishing between rows excluded by filters and manually hidden rows
LimitationsPhysical device screen operations, UI differences in Excel for the Web, and print results remain unverified

In an Excel sales table, if you display "Tokyo only" and the SUM function total still reflects the nationwide figure, it is not a malfunction. By default, SUM aggregates rows that are hidden by filters.If you want to sum only the rows currently displayed on the screen, use the SUBTOTAL function.However, rows excluded by filters are handled differently from rows manually hidden via right-click or similar methods. Confusing this distinction can lead to errors where the same file yields different figures for different users.

Test with a mock sales table

In a new blank workbook, enter data into just two columns instead of the following four columns. There is no need to directly edit an existing sales ledger.

CellColumn A: RegionColumn B: Amount
Row 1RegionAmount
Row 2Tokyo100
Row 3Osaka200
Row 4Tokyo300
Line 5Osaka400

Place the aggregate formulas outside the table. Enter =SUM(B2:B5) in D2, =SUBTOTAL(9,B2:B5) in D3, and =SUBTOTAL(109,B2:B5) in D4. By default, all of these are assumed to be 1000. The first arguments 9 and 109 in the formulas both represent "Sum", with 109 specifically designated to exclude manually hidden rows.

Next, select A1:B5, enable Data -> Filter, and filter the region candidates to "Tokyo" only. The displayed amounts are 100 and 300. Here, the expected result based on official specifications is that the SUM in D2 remains 1000, while the SUBTOTAL in D3 and D4 becomes 400.Rows excluded by the filter are not aggregated by either 9 or 109.Remembering that "only 109 supports filters" is a misconception.

OperationSUMSUBTOTAL(9)SUBTOTAL(109)
Show All Rows100010001000
Filter Region to Tokyo Only1000400400
Manually hide row 3 after clearing filters10001000800

The table shows expected values calculated from official Microsoft specifications, not measured values from actual hardware. Verify whether they match in your own Excel.

Changing only one condition: Hiding rows instead of filtering

Clear the filter to restore all rows. Next, right-click row number 3 and select Hide. The row for Osaka 200 will be hidden. In this case, 9 returns 1000 because it includes manually hidden rows, while 109 returns 800 because it excludes manually hidden rows. This is the simplest experiment to observe the difference between 9 and 109.

Why do the results differ even though both are "hidden rows"? Excel treats rows hidden because they do not match filter criteria differently from rows manually hidden by the user. According to Microsoft Support,the SUBTOTAL functionspecifies that 1 through 11 include manually hidden rows, 101 through 111 exclude them, and filter-excluded rows are ignored regardless of the function number.

Troubleshooting when numbers do not match

First, check separately whether "filters are applied," "row numbers are discontinuous," or "there are manually hidden rows." When SUM and SUBTOTAL results differ, verify the display state rather than assuming the formula is incorrect. Note that clearing filters does not automatically unhide manually hidden rows.

Next, check if the formula's reference range is consistently B2:B5. Including the total cell within the reference range can cause circular references or unintended aggregations. Also check for mixed text strings that look like numbers, blank cells, or error values. Troubleshooting is easier if you check the formula bar for the target cells rather than relying solely on appearance.

Furthermore, SUBTOTAL is designed for vertical column data. Hiding columns for horizontally arranged monthly figures does not guarantee the 109 behavior of "excluding manually hidden rows." Additionally, when another SUBTOTAL exists within the reference range, special handling is applied to avoid double counting. Do not determine correctness solely by comparing with a simple SUM in reports containing multiple summary rows.

Defining the meaning of totals for business use

If a sales representative narrows down regions to check the amount for "only the deals currently visible on the screen," SUBTOTAL(109, range) is a straightforward choice. On the other hand, if a monthly report displays fixed sales figures for all branches, using SUM, which is unaffected by the display state, may be more appropriate. Which is correct depends on the purpose of the report.

In shared workbooks, clearly communicate to users how the display state of whoever manipulated the filters links to the aggregation results. It is important not to mix confirmed report values with filtered work-in-progress aggregations under the same "Total" label. When pasting screen captures of filter results into documents, keeping the condition names and aggregation formulas together allows you to reproduce the numbers later.

Official information and unverified items

The primary source isMicrosoft Support "SUBTOTAL function"We verified constraints such as the distinction between 9 and 109, filter handling, and vertical ranges. The values 1000, 400, and 800 in the article are expected values derived from fictitious data and specifications, not operation results from an actual Excel instance. Screen displays, version differences, co-authoring, and rendering after publication on WordPress have not been verified.

By deciding beforehand whether you want to aggregate only the rows displayed on the screen or the entire range including hidden rows, you can properly use SUM, SUBTOTAL(9), and SUBTOTAL(109) according to your objective.

Document information

Article title
Excel Total Doesn't Change After Filtering? Comparing SUBTOTAL 9 and 109
Published
Updated
Source
https://papanda925.com/?p=18709&lang=en

License: Text and original figures for which this site holds the relevant rights are available under CC BY 4.0 , unless otherwise noted. This article may include content created or edited with generative AI. If code has a separate license notice or a linked GitHub repository license, that license takes precedence for the code. Quotations, third-party materials, images, and trademarks are excluded from this license. Usage policy

Copied title and URL