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)
- Test with a mock sales table
- Changing only one condition: Hiding rows instead of filtering
- Troubleshooting when numbers do not match
- Defining the meaning of totals for business use
- Official information and unverified items
Supported Environments, Capabilities, and Limitations (Verified: November 17, 2026)
| Item | Target / Condition |
|---|---|
| Target Products | Excel for Microsoft 365 (Windows), Excel 2024 and 2021 |
| Operation Location | New workbook in desktop Excel, data filtering, and row hiding |
| License / Permissions | Environment where Excel is available. Copilot, administrator privileges, and external services are not required. |
| Save Location | Practice workbook on local PC. Uploading to OneDrive or SharePoint is not required. |
| Capabilities | Calculate totals while distinguishing between rows excluded by filters and manually hidden rows |
| Limitations | Physical 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.
| Cell | Column A: Region | Column B: Amount |
|---|---|---|
| Row 1 | Region | Amount |
| Row 2 | Tokyo | 100 |
| Row 3 | Osaka | 200 |
| Row 4 | Tokyo | 300 |
| Line 5 | Osaka | 400 |
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.
| Operation | SUM | SUBTOTAL(9) | SUBTOTAL(109) |
|---|---|---|---|
| Show All Rows | 1000 | 1000 | 1000 |
| Filter Region to Tokyo Only | 1000 | 400 | 400 |
| Manually hide row 3 after clearing filters | 1000 | 1000 | 800 |
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.

