Yes. To sum visible rows while excluding both filtered-out rows and rows hidden manually, use SUBTOTAL with function number 109; with amounts in B2:B100, enter =SUBTOTAL(109,B2:B100). A regular SUM includes hidden values, so the right choice depends on how the rows were hidden and what you want the total to represent.
Why SUM includes hidden cells
The SUM function adds the numeric values in its references without checking whether their rows are currently visible. Hiding a row changes how the worksheet looks, not whether its contents exist or are included in a calculation. For example, hiding a row to simplify a report does not remove that row’s amount from a nearby SUM formula.
That behavior is often useful. A hidden row may contain a legitimate transaction, expense, or measurement that should remain part of an overall total. The complication arises when you want a total that follows the visible view—for example, a subtotal of sales left on screen after filtering a list. In that case, choose a function designed to respond to row visibility.
There are two common ways rows become invisible:
- Filtered out: A filter hides rows that do not meet the selected criteria.
- Hidden manually: Someone hides rows with the row headings, the Format menu, or an outline/grouping control.
Some formulas treat these situations differently. Knowing which one applies prevents a total that looks plausible but includes values you meant to leave out.
Use SUBTOTAL to total visible rows
For a live total that changes when rows are filtered, use SUBTOTAL. Its first argument selects the operation. For sums, function number 9 includes manually hidden rows, while 109 excludes them. Both versions exclude rows removed by a filter. Therefore, 109 is generally the appropriate choice when you want to add only rows currently visible in a vertical list.
With amounts in B2:B100, including the header outside the range, enter =SUBTOTAL(109,B2:B100). The result updates when you apply or change a filter, and it also leaves out manually hidden rows. The range should contain the detail values you want to add, not unrelated numbers elsewhere in the sheet.
With amounts in B2:B100, use =SUBTOTAL(9,B2:B100) instead if you want filtered-out rows excluded but manually hidden rows included. This distinction matters when a workbook uses manual hiding for presentation but still expects those values to count in its subtotal.
In Microsoft 365 for Windows, you can apply a filter by selecting a cell in the data and choosing Data > Filter. Use the arrow in the column heading to choose the values or conditions to display. The subtotal formula recalculates as the visible records change. On Mac, the Data tab also provides the Filter command; in Excel for the web, use the Data tab’s filter controls. The formula itself works the same way in these versions.
A worked example: filtered versus manually hidden
Suppose amounts are in B2:B6: 10, 20, 30, 40, and 50. If row 3, containing 20, is hidden manually, and a filter also removes row 5, containing 40, the currently visible values are 10, 30, and 50. A visible-row total should be 90.
With those amounts in B2:B6, enter =SUBTOTAL(109,B2:B6). It returns 90 because it excludes both the manually hidden row and the filtered-out row. With the same layout, =SUBTOTAL(9,B2:B6) returns 110: it leaves out the filtered 40 but includes the manually hidden 20. With the same layout, =SUM(B2:B6) returns 150 because it includes all five values.
This example also shows why testing a formula only before filtering can be misleading. Before any rows are hidden, both subtotal versions return the same result as the sum. Their different behavior becomes apparent only when visibility changes.
How Excel handles hidden rows and filters
SUBTOTAL is intended for lists and ranges in which visible subtotals matter. When a filter excludes records, SUBTOTAL ignores them whether you use function number 9 or 109. The choice between those numbers concerns rows hidden manually: 9 counts them; 109 does not.
Excel also prevents subtotals from being counted again when they are nested within a referenced range. For example, if one subtotal formula appears inside the range of another, the outer SUBTOTAL ignores the inner subtotal result. That helps avoid accidental double counting in layered reports.
The 100-series function numbers are available for several operations, not only sums. For example, 101 and 109 are both sum operations with different treatment of manually hidden rows; 109 is the sum version that ignores them. The function evaluates rows in the reference, so do not assume it will ignore a manually hidden column. If your data runs across columns and you need visibility-sensitive calculations, test the arrangement or reshape the data into a vertical list.
Choosing between SUM, SUBTOTAL, and AGGREGATE
Choose the function based on the meaning of the total, rather than simply picking the one that gives the smallest number:
- Use SUM when every value in the range should count, regardless of filters or hidden rows. It is a straightforward choice for fixed totals and many calculations elsewhere in a model.
- Use SUBTOTAL(9,…) when the total should respond to filters but should still include manually hidden rows.
- Use SUBTOTAL(109,…) when the total should include only visible rows, excluding both filtered-out and manually hidden rows.
- Consider AGGREGATE when you need a sum that can also ignore errors or other unwanted values, or when you need one of its other supported calculations.
For a simple visible-row sum, SUBTOTAL(109,...) is usually easiest to read. AGGREGATE offers additional options, but those options can make a formula harder for another person to interpret if the workbook only needs a basic subtotal.
With amounts in B2:B100, the AGGREGATE sum operation set to ignore manually hidden rows is =AGGREGATE(9,5,B2:B100). The function number 9 requests a sum; option 5 ignores hidden rows. Filtered-out rows are excluded by AGGREGATE as well. AGGREGATE is available in Excel 2010 and later, including Microsoft 365. For ordinary filtered lists, SUBTOTAL is often the clearer choice.
Put a subtotal in an Excel table
Excel tables make filtered lists easier to manage. To create one, select the data and choose Insert > Table, confirming whether the first row contains headings. Table filter buttons let you show selected records, and the table can expand as you add data.
To add a built-in total row, click in the table, open the Table Design tab, and select Total Row. In the total-row cell for a numeric column, choose Sum from its drop-down list. Excel uses a subtotal-style calculation so the displayed total responds to filtering. If you need the total row to disregard manually hidden rows too, inspect the formula Excel created and use the 109 version if it is not already there.
A table total row is convenient, but it is not a substitute for understanding the formula. A separate report cell may be easier to position or format, and a workbook may need different behavior for different totals. You can use a formula such as =SUBTOTAL(109,Table1[Amount]) when the table is named Table1 and its numeric column is named Amount. Excel table names and column headings must match your workbook exactly.
Common problems and checks
- The total does not change when you hide a row. Check whether the formula uses 9 rather than 109. Function number 9 includes manually hidden rows.
- Filtered records still seem to be counted. Confirm that the range is the one being filtered and that the filter actually hides those records. Both 9 and 109 should exclude rows filtered out of the referenced list.
- The result is zero or unexpectedly low. Check that the referenced cells contain numbers, that the range points to the intended column, and that the cells are not numbers stored as text.
- The formula includes a heading or unrelated value. Adjust the reference to cover only the intended data rows. A text heading is not added, but an overly broad range can include other numeric cells.
- A hidden column still contributes to a result. SUBTOTAL’s hidden-row behavior does not mean it ignores manually hidden columns. The function is designed for row-based lists.
Also check for additional subtotal formulas inside the range. SUBTOTAL ignores nested SUBTOTAL results, but a plain SUM elsewhere in the range is still an ordinary numeric value and may be added. If the worksheet contains grouped data or multiple calculation layers, verify that each formula covers the intended detail rows.
Frequently Asked Questions
Does SUM ignore filtered rows in Excel?
No. SUM includes values in rows hidden by a filter. Use SUBTOTAL(9,range) or SUBTOTAL(109,range) when the total should respond to filtering.
Which SUBTOTAL number ignores manually hidden rows?
Use 109 for a sum that ignores manually hidden rows as well as rows excluded by a filter. Use 9 if filtered-out rows should be excluded but manually hidden rows should still count.
Does SUBTOTAL work with manually hidden rows?
Yes. The 100-series version for sums, 109, excludes manually hidden rows. The 9 version includes them. Both exclude rows hidden by a filter.
Can I sum visible cells across columns?
SUBTOTAL’s visibility behavior is designed for hidden rows, not manually hidden columns. If your values are arranged horizontally, hiding a column will not make the formula a dependable visible-column sum; consider a different layout or a formula designed for the specific arrangement.
Will the total update when I change a filter?
Yes. A SUBTOTAL formula referencing the filtered list recalculates to reflect the rows currently included in the filter. If it does not, confirm that the reference covers the filtered data and that Excel calculation is not set to manual.
Related
- Excel Not Responding When Copying and Pasting? Fixes
- Excel Not Updating Links From Other Workbooks? Fixes
- Excel Not Keeping Formatting? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Grouped Scatter Plot: Setup, Steps, and TipsTo make a grouped scatter plot in Excel, give each group its own data series, with one range…
- Out of Office in Outlook Exchange: Setup and SchedulingTo set an out-of-office message for an Exchange mailbox, open Outlook’s Automatic Replies settings, turn on automatic replies,…
- Conditional Formatting for Negative Values in ExcelIn Excel for Microsoft 365 on Windows, select the cells you want to check, then choose Home >…
- Excel File Password: What It Means and How to Set OneAn Excel file does not have a universal password: the person who protects it chooses one. In Excel…
- Can You Insert a Page Break in Excel? Yes, Here’s HowYes. In Excel for Microsoft 365 on Windows, select the row below or the column to the right…
- Add a Shared Mailbox to New Outlook: Setup and FixesTo add a shared mailbox in New Outlook for Windows, right-click your account name in the folder pane,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.