To calculate a total for visible rows in Excel, use =SUBTOTAL(109,B2:B100), replacing the range with your data. The 109 tells Excel to sum cells while ignoring rows hidden by a filter or hidden manually; use 9 instead if you want manually hidden rows included.
What SUBTOTAL Means by Visible Cells
Excel’s SUBTOTAL function is designed for lists and ranges where some rows may be filtered out or hidden. It applies a calculation, such as sum or average, to the cells you reference, while excluding rows that are not currently included in the visible result. That makes it useful for a filtered sales list, inventory table, expense log, or other changing dataset.
There are two kinds of row hiding to distinguish. A filter hides rows that do not match the selected criteria. Manually hiding a row, including by using an outline or group, removes it from view without filtering it. SUBTOTAL always leaves filtered-out rows out of its result. Whether it includes manually hidden rows depends on the function number you choose.
“Visible cells” here means cells in visible rows within the referenced range. SUBTOTAL is not a general-purpose way to calculate only cells that happen to be visible after selecting scattered cells, and its hidden-row options do not mean it ignores hidden columns. For a typical vertical list with filters, however, it provides the changing total most people want.
Choose the Right Function Number
The first argument of SUBTOTAL determines the operation and whether manually hidden rows count. The basic codes 1 through 11 include manually hidden rows but still exclude rows removed by a filter. The corresponding codes 101 through 111 exclude both filtered-out and manually hidden rows. For a visible-only sum, the relevant choices are 9 and 109.
- 9: SUM, including manually hidden rows.
- 109: SUM, excluding manually hidden rows as well as filtered-out rows.
- 1 and 101: AVERAGE, with the same distinction.
- 2 and 102: COUNT for numeric cells; 3 and 103: COUNTA for nonempty cells.
- 4 and 104: MAX; 5 and 105: MIN.
- 6 and 106: PRODUCT; 7 and 107: sample standard deviation; 8 and 108: population standard deviation.
- 10 and 110: sample variance; 11 and 111: population variance.
Use the 101–111 family when the instruction really is “ignore all hidden rows.” If users only apply filters and never hide rows manually, 9 and 109 return the same sum. Choosing 109 is a common defensive choice for a visible-rows total because a manually hidden row will not unexpectedly remain in the result.
Worked Example: A Filtered Sales List
Suppose a worksheet has Region in A1:A6 and Sales in B1:B6. The headers are in row 1, and the five sales values in B2:B6 are 120, 80, 50, 30, and 20. If you filter the Region column to show only North, and the North records have sales of 120 and 50, the visible sum should be 170.
With sales in B2:B6 and the header excluded from the range, enter =SUBTOTAL(109,B2:B6) in a cell outside the filtered data. The result is 170 while only the North rows are shown. Change the filter to another region and the result recalculates for the rows now visible. Clear the filter and all five values are included, for a total of 300.
The same example shows why the function number matters. If the 30 row is manually hidden after clearing the filter, =SUBTOTAL(9,B2:B6) returns 300 because code 9 includes manually hidden rows. With sales in B2:B6 and the 30 row manually hidden, =SUBTOTAL(109,B2:B6) returns 270. If a filter also excludes rows, both codes ignore those filtered-out rows.
How to Add a Visible-Only Total
- Arrange the data as a list with a single header row and consistent columns. Remove completely blank rows or columns inside the list if they would interfere with selecting the full dataset.
- Select a cell in the list. On Excel for Microsoft 365 for Windows, choose Data > Filter to add filter arrows to the headers. If the data is already an Excel table, its headers normally have filter controls already.
- Put the total formula outside the filtered list, such as below it with enough space to prevent it from becoming a data row. With sales in B2:B100, enter
=SUBTOTAL(109,B2:B100). - Use the header arrows to filter the records. The formula updates as rows are included or excluded. Clear a filter to see the total for the full range again.
Keeping the formula outside the filtered range is important for clarity and avoids treating the total as another record. If you convert the list to a table with Insert > Table, you can also turn on its Total Row from the Table Design tab. In the Total Row cell for a column, choose Sum from the drop-down; Excel uses a SUBTOTAL calculation for table totals, so the displayed value responds to filtering.
Using SUBTOTAL for Other Visible-Row Calculations
SUBTOTAL is not limited to addition. For example, an average of the sales remaining after a filter can be calculated with the average code. With sales in B2:B100, enter =SUBTOTAL(101,B2:B100) to average the visible rows while also excluding any manually hidden rows. If the visible sales are 120 and 50, the result is 85.
Choose COUNT when you need the number of numeric entries in the visible rows, such as how many records have a numeric sales value. Choose COUNTA when you need to count nonempty entries, such as populated invoice IDs. COUNTA considers a cell containing a formula that returns an empty text string to be nonempty, so it may count more cells than a person expects if the column contains such formulas.
MAX and MIN can show the highest or lowest visible value without changing the underlying data. This can help answer questions such as “What is the largest order among the records in this region?” Remember that the function number sets the operation; it does not change how filters themselves work. Use the 100-series code when manually hidden rows should be omitted too.
Important Limits and Behaviors
SUBTOTAL ignores other SUBTOTAL formulas within a referenced range. This is intentional: if a list contains subtotals for separate groups, a grand subtotal can avoid counting those intermediate subtotal results a second time. It does not automatically ignore every other kind of total formula, however. A SUM formula inside the range can still be included as an ordinary numeric result, so avoid putting unrelated totals inside the source range.
Filtering and hiding columns are different. The 101–111 codes respond to hidden rows, not hidden columns. If you hide a column that is part of the referenced range, that alone does not make SUBTOTAL exclude its values. Also make sure the formula’s range covers the records you intend to analyze, not the header or a separate summary area.
SUBTOTAL recalculates when filter results change. It is useful when the visible subset is determined by Excel’s filter controls, but it is not a replacement for every conditional calculation. If you need to sum records meeting a rule whether or not their rows are visible, SUMIF or SUMIFS may be more appropriate. Those functions evaluate criteria in the data; they do not mean “sum only what is currently visible.”
Common Problems and Fixes
- The total stays larger than expected: Check whether the overlooked rows were manually hidden. If the formula uses 9, those rows are included; change it to 109 to exclude them.
- The total does not change after filtering: Confirm that the filtered records are inside the formula’s range and that the cells contain numeric values. Check that the filter is applied to the intended list rather than a separate range.
- The result is zero or incomplete: Some apparent numbers may be stored as text, or the formula may omit rows at the bottom of the list. Inspect the cells and expand the referenced range as needed.
- A subtotal seems counted twice: SUBTOTAL ignores nested SUBTOTAL results in its range, but a separate SUM or manually entered total can still be included. Keep summary formulas outside the data range where possible.
- The formula displays as text: The cell may be formatted as Text, or the entry may start with an apostrophe. Change the cell format to General, then re-enter the formula.
SUBTOTAL Compared with SUM and SUMIFS
SUM adds the values in its range regardless of whether their rows are hidden by a filter. It is suitable for an unconditional total, but not for a total intended to follow filter selections. SUBTOTAL is the simpler choice when the visible set should control the answer, such as when someone filters a report by month or department.
SUMIFS adds values that meet one or more stated criteria. For example, it can total sales for North even if the worksheet is not filtered to North. That makes it useful for fixed or formula-driven criteria, but it does not automatically restrict the total to rows visible after a filter. These tools answer different questions: SUMIFS asks which records meet the criteria you specify; SUBTOTAL responds to which rows are currently included in its range.
Frequently Asked Questions
What formula sums only visible cells in Excel?
For a visible-row sum that ignores both filtered-out and manually hidden rows, use =SUBTOTAL(109,range), replacing range with the cells to add, such as B2:B100. Use code 9 if manually hidden rows should still count.
Does SUBTOTAL ignore rows hidden by a filter?
Yes. All SUBTOTAL function numbers exclude rows removed by a filter. The difference between codes 9 and 109 is whether manually hidden rows are included: 9 includes them, while 109 excludes them.
Can SUBTOTAL count visible cells instead of summing them?
Yes. Use code 102 for a count of numeric cells that excludes manually hidden rows, or code 103 for a count of nonempty cells with the same hidden-row behavior. Both also ignore filtered-out rows.
Why does SUBTOTAL include a hidden row?
The formula may use a code from 1 to 11. Those codes include manually hidden rows even though they exclude filtered rows. Choose the matching code from 101 to 111 if manual hiding should also remove the row from the calculation.
Does SUBTOTAL ignore hidden columns?
No. Its 101–111 codes control how hidden rows are treated, not hidden columns. Hiding a column does not by itself remove that column’s values from the formula’s calculation.
Does SUBTOTAL work with an Excel table?
Yes. Table filters can change which records are visible, and SUBTOTAL responds to that filtered view. Excel’s Total Row also uses SUBTOTAL-style calculations so the total can adjust as table records are filtered.
Related
- X++ Enum Values in SQL: How Stored Numbers Work in D365
- D&A on the Balance Sheet: Where It Appears and Why
- How to Add Speaker Notes to Your PowerPoint Slides
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Nested Formula Examples and How They WorkExcel nested formulas place one function inside another, so a calculation or test can supply the input for…
- Sort Excel Workbook Tabs Alphabetically in ExcelExcel does not have a built-in command that automatically sorts workbook tabs alphabetically. In Microsoft Excel for Microsoft…
- SUMIF Text Contains a Word in ExcelTo sum values when a cell contains a particular word, use SUMIF with asterisks around the word in…
- Excel UNIQUE Formula: Return a List Without DuplicatesYes. In Microsoft 365 or Excel 2021 and later, use =UNIQUE(A2:A8) to return the distinct values from A2:A8…
- Excel Dynamic Filter Multiple Criteria: Formulas and TipsTo dynamically filter rows in Excel by multiple conditions, use the FILTER function and combine criteria with multiplication…
- SUMPRODUCT in Excel: Examples, Criteria, and TipsA simple example of SUMPRODUCT in Excel is =SUMPRODUCT(B2:B5,C2:C5): if B2:B5 contains quantities and C2:C5 contains unit prices,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.