Excel SUM and Hidden Cells: What Gets Included in Totals?

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, SUM includes numbers in hidden rows and columns, including rows hidden by a filter. To total only visible rows, use SUBTOTAL: choose function number 9 to include manually hidden rows, or 109 to exclude them as well as filtered-out rows.

How Excel treats hidden cells

Hiding a row or column changes what is displayed, not what is stored in the worksheet. A standard SUM evaluates the cell references in its range whether those cells are visible or not. If a range contains four amounts and one of their rows is hidden, the hidden amount still contributes to the total.

The same principle applies when you use a filter. Filtering a list makes some records temporarily invisible, but does not remove them from the worksheet or from a regular SUM. That is why a total beneath a filtered list can look too large: it may still include every record, not just the records currently on screen.

This behavior is useful when the goal is a complete total regardless of the current view. For example, a yearly total should not change just because someone hides a few detail rows to make a report easier to read. The important question is whether your total is meant to describe the entire range or only the visible records.

Filtered rows and manually hidden rows

Excel distinguishes between rows hidden by a filter and rows hidden manually with the row commands or by changing row height. For SUBTOTAL, rows excluded by a filter are left out of the result. Its function number also determines how it treats manually hidden rows.

  • SUBTOTAL(9,range) adds visible rows and manually hidden rows, but excludes rows removed from view by a filter.
  • SUBTOTAL(109,range) adds only visible rows, excluding both filtered-out rows and manually hidden rows.

For a list of numbers in A2:A5, suppose the values are 100, 200, 300, and 400, and row 4 (the 300) is manually hidden. With that layout, =SUBTOTAL(9,A2:A5) returns 1,000, while =SUBTOTAL(109,A2:A5) returns 700. If the 300 row is instead hidden by a filter, both versions return 700. A normal =SUM(A2:A5) returns 1,000 in either situation.

Function numbers 1 through 11 include manually hidden rows; the corresponding numbers 101 through 111 exclude them. The 9 and 109 pair are the SUM versions. This difference is easy to overlook when copying a formula from an existing report, so check the first argument if manually hidden rows matter.

Choose a formula for the result you want

Before changing a total, decide what it should represent. A workbook may need both an all-records total and a visible-rows subtotal, and those are different measures. Choose based on the intended meaning, not merely on which number looks more plausible.

  • Use SUM for a total of every cell in a range, whether its rows or columns are visible.
  • Use SUBTOTAL with 9 when filtered-out records should be excluded, but manually hidden rows should still count.
  • Use SUBTOTAL with 109 when the total should reflect only currently visible rows, excluding both filtered-out and manually hidden rows.
  • Keep a separate all-records total if readers need to compare the visible subtotal with the full dataset.

With amounts in A2:A5, the complete-range formula is =SUM(A2:A5); it returns 1,000 for the example values 100, 200, 300, and 400. With the same cell layout, =SUBTOTAL(109,A2:A5) returns 700 when the row containing 300 is hidden, whether manually or by a filter. These formulas are ordinary worksheet formulas and recalculate when relevant data or visibility changes.

Set up a visible-rows total

For a filtered table or list, place a subtotal in a cell outside the list’s data rows, then use the appropriate SUBTOTAL formula. Keeping the result outside the range helps avoid including the result itself or creating confusing totals.

  1. Identify the cells containing the values to add, such as A2:A100.
  2. Choose 9 if manually hidden rows should remain included, or 109 if they should be excluded.
  3. Enter the formula in a cell outside the data range and press Enter.
  4. Apply or change the filter, then confirm that the subtotal reflects the records still visible.

For amounts in A2:A100, with the total entered in A101, =SUBTOTAL(109,A2:A100) sums visible amounts and excludes manually hidden and filtered-out rows. If the list is filtered to show only certain categories, the result changes with the visible records. This makes SUBTOTAL useful for reports in which users interact with filters.

In a formatted Excel Table, filters are available in the column headers, and a Total Row can be enabled from the Table Design tab. Excel can provide a subtotal formula in that row; inspect the formula if you need a particular rule for manually hidden rows. A formula outside the table is also an option when you want explicit control over the function number.

Columns, selection, and what SUBTOTAL does not do

SUBTOTAL is designed to respond to hidden rows and filtered records in a vertical range. It does not mean “add every cell that happens to be visible on the screen.” In particular, hiding columns does not make values in those columns disappear from a subtotal. If a formula’s referenced range spans hidden columns, those cells can still contribute.

For example, imagine monthly amounts in B2:M2 and some month columns hidden to simplify the view. A row-oriented sum across B2:M2 still includes the values in those hidden columns. If the intended result is the sum of selected months, use a range or formula that explicitly identifies those months instead of relying on column visibility.

Similarly, SUBTOTAL is not a general visibility detector for every worksheet arrangement. It is most straightforward for vertical lists where each record occupies a row and the values to add sit in one column. If a report is laid out horizontally, or visibility rules are more complicated, define the calculation using explicit cell references or another approach suited to the layout.

Common causes of surprising totals

If a total does not match what you can see, first determine how the rows were hidden. Filtering, manually hiding rows, and hiding columns do not all have the same effect on subtotal formulas. Then check the formula itself and the exact range it covers.

  • The formula is SUM. It includes hidden and filtered-out values by design.
  • The wrong SUBTOTAL number is used. Number 9 includes manually hidden rows; 109 excludes them.
  • The range omits or includes extra cells. Check the first and last references, and make sure the formula cell is not accidentally part of the range.
  • Columns are hidden, not rows. Neither a normal sum nor a row-oriented subtotal treats hidden columns as excluded values.
  • A subtotal is nested inside another subtotal. Excel excludes nested SUBTOTAL results from another SUBTOTAL calculation, which can make a combined range behave differently from a plain sum.

Also consider whether the displayed values are actually numbers. Cells containing text that looks like a number may not be added as expected. If the formula’s range and visibility behavior are correct but the result is still unexpected, inspect the source cells and the formula bar rather than assuming that hiding is the cause.

SUM, SUBTOTAL, and alternatives

SUM is the clearest choice for a fixed total of all referenced values. SUBTOTAL is usually the better fit for an interactive filtered list because filtered-out records do not affect its result. Its 9-versus-109 distinction gives you a choice about manually hidden rows while keeping the formula relatively simple.

Excel also has AGGREGATE, which can perform a sum while ignoring hidden rows. It offers additional options for handling errors and nested functions, but it is more complex than most filtered-list totals require. For a straightforward “sum visible rows” result, SUBTOTAL(109,...) is easier for many readers to interpret. Whatever method you use, label the result clearly—for example, “Visible rows” versus “All records”—so a changing filtered total is not mistaken for a permanent grand total.

Do not use a manually selected group of visible cells as a substitute for a durable formula unless the result is intentionally a one-time calculation. A hand-picked range can become outdated as rows are added, removed, or filtered differently. A formula that expresses the desired rule is easier to audit and more likely to keep reporting the right meaning.

Frequently Asked Questions

Does Excel SUM ignore filtered rows?
No. SUM includes values in rows hidden by a filter. Use SUBTOTAL when the result should respond to filtering.

Which SUBTOTAL formula sums only visible rows?
Use function number 109 for a sum that excludes filtered-out and manually hidden rows. For example, with amounts in A2:A20, =SUBTOTAL(109,A2:A20) totals the visible amounts.

What is the difference between SUBTOTAL 9 and 109?
Both perform a sum and exclude rows hidden by a filter. Number 9 includes manually hidden rows, while 109 excludes them.

Does SUBTOTAL ignore hidden columns?
No. Its hidden-row behavior does not automatically remove values in hidden columns from a referenced range. Select the desired columns explicitly if column visibility should affect the calculation.

Will a SUBTOTAL update when I change a filter?
Yes. A SUBTOTAL formula recalculates to reflect rows included or excluded by a filter. A normal SUM continues to include the filtered-out records.

Why does my total change when I hide a row manually?
If the formula uses SUBTOTAL with a function number from 101 to 111, manually hidden rows are excluded. A number from 1 to 11 includes them; a standard SUM includes them too.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.