Excel SUM Visible Rows: Add Filtered Data Correctly

How-to basics · Updated November 18, 2026

With values in B2:B100, use =SUBTOTAL(109,B2:B100) to add the visible rows and exclude rows removed by a filter or hidden manually. If you only need filtered-out rows excluded but want manually hidden rows included, use function number 9 instead of 109.

What “sum visible” means in Excel

In Excel, a visible-row total is a sum that updates when you filter a list. For example, a sales column might contain orders from several regions. Filtering the Region column to show only North should make the total reflect only the displayed North orders, rather than every order in the original data.

The ordinary SUM function adds every number in its range, whether its row is currently displayed or not. That is useful for a fixed total, but it can be misleading beside a filtered list: the rows disappear while the total stays the same. SUBTOTAL is designed for calculations on lists and filtered ranges, so it is usually the simplest choice for a visible-only sum.

“Visible” can also mean rows that someone hid manually, not just rows excluded by a filter. Excel treats those cases differently depending on the SUBTOTAL function number. Choosing the right number prevents a total from including amounts that a reader cannot currently see.

How SUBTOTAL handles filters and hidden rows

SUBTOTAL takes a function number and one or more ranges. Function number 9 means SUM. Function number 109 also means SUM, but it additionally excludes manually hidden rows. Both versions ignore rows removed by an Excel filter.

  • 9: adds filtered-in rows, including rows hidden manually.
  • 109: adds filtered-in rows and excludes rows hidden manually.
  • Other function numbers: provide calculations such as AVERAGE, COUNT, MAX, or MIN with comparable visibility behavior.

For a visible-only total that should respond to both filtering and manual hiding, with amounts in B2:B100, use =SUBTOTAL(109,B2:B100). If a row is hidden by a filter, it is omitted. If a row is hidden using Hide, it is also omitted. Unhide it and the result can include it again, provided it passes the active filter.

One useful detail is that SUBTOTAL ignores other SUBTOTAL results within its reference. This helps avoid double-counting when a list already contains subtotal rows. The formula still depends on the range you give it: a subtotal placed outside that range is not included, and an incorrect or incomplete range can produce an incorrect total.

Choose between function numbers 9 and 109

Use 109 when the displayed total should match every row currently visible on the worksheet. This is the less surprising choice for reports where users may hide rows by hand to focus on a subset. It also gives a consistent rule: if the row is hidden, its amount does not contribute.

Use 9 when the report should respond to filters but not to manual hiding. A common reason is that someone temporarily hides a row for presentation but still wants its value counted in the filtered result. With amounts in B2:B100, that version is =SUBTOTAL(9,B2:B100). It still excludes rows filtered out of the list.

Neither number means “sum only the cells I can see on screen” in every possible sense. For example, hiding a column does not make values in that column disappear from a row-based SUBTOTAL range. These function numbers govern hidden rows and filtered records, not whether a column is visible, whether a cell is covered by another window, or whether the worksheet is scrolled away from it.

Enter a visible-row total beside a filtered list

For a normal range, enter the SUBTOTAL formula in a cell outside the rows being filtered. Keeping the result above or below the list makes it easy to find and avoids accidentally filtering the total as if it were another record.

  1. Put a clear header at the top of each data column, such as Region, Date, and Amount.
  2. Select a cell in the data, then choose Data > Filter on Excel for Microsoft 365 for Windows.
  3. Open the filter arrow in the relevant header and select the values to display.
  4. In a cell outside the filtered records, enter the SUBTOTAL formula for the amount range.
  5. Change the filter or clear it to confirm that the total updates with the displayed records.

On Excel for Mac, the Data tab also has the Filter command; the exact ribbon layout can vary with the window size. Excel for the web also supports filtering and SUBTOTAL, though its ribbon arrangement may differ. The function itself works the same way: filtered-out rows are excluded.

For example, if B2:B5 contains 100, 250, 80, and 70, and a filter leaves only the first and third records displayed, a 109 subtotal over B2:B5 returns 180. Clear the filter and it returns 500. That changing result is the key distinction from a regular SUM.

Use an Excel Table for totals that grow with your data

If the list is likely to gain rows, convert it to an Excel Table rather than maintaining a fixed range. Select a cell in the data and choose Insert > Table, or use Ctrl+T on Windows. On Mac, Command+T is the usual shortcut for creating a table. Confirm that the range has headers when appropriate.

Tables expand when new records are entered directly beneath them, and their filter arrows are built into the header row. To add a table total, select a cell in the table, open the Table Design tab, and turn on Total Row. In that row, open the total cell’s dropdown and choose Sum for the relevant numeric column. Excel uses a SUBTOTAL-based calculation for table totals, so the result responds to filters.

If you prefer to write the formula yourself, with a table named Sales and a column named Amount, use =SUBTOTAL(109,Sales[Amount]). The structured reference follows the table column as records are added or removed. Check the table and column names in the Table Design tab if Excel reports a name error; a reference must match the actual names in the workbook.

A Table is convenient, but not essential. A normal range with Data > Filter and a correctly sized SUBTOTAL formula works as well. The main advantage of the Table is that its range and references are easier to maintain as a dataset grows.

Common problems and ways to check the result

  • The total does not change after filtering: Check that the formula uses SUBTOTAL, not SUM, and that the formula’s range covers the filtered data. Also confirm that the filter is applied to the same list.
  • Hidden rows still count: If you used function number 9, that is expected for manually hidden rows. Switch to 109 if those rows should be excluded too.
  • The total is zero or too small: Some amounts may be stored as text rather than numeric values. Check the cells’ contents and number formats; changing the display format alone does not always convert text into numbers.
  • The total omits a record: Check the active filter criteria, blank rows that may split the range, and whether the range stops before the latest records.
  • The formula shows an error: Confirm that the range reference and parentheses are correct and that a structured reference uses the real table and column names.
  • A hidden column still seems to count: SUBTOTAL’s 9 and 109 options address hidden rows, not hidden columns. Hiding a column does not generally remove its values from a row-range total.

For a quick audit, clear all filters and compare the visible-row subtotal with the expected total for the complete list. Then apply one simple filter whose matching records are easy to identify and check that the displayed values add to the reported result. This can reveal an incorrect range or data stored as text before the workbook is shared.

SUBTOTAL compared with SUM and AGGREGATE

Use SUM when the result should always include every value in a range, regardless of filtering. Use SUBTOTAL when the total should follow a filter or should ignore hidden rows. This is a functional difference, not just a display choice: filtering changes which records contribute to a SUBTOTAL result.

AGGREGATE is another option for more specialized calculations. It can perform SUM and other operations while offering options to ignore hidden rows, errors, or nested subtotal calculations. For a straightforward filtered total, SUBTOTAL is usually easier to read and maintain. AGGREGATE is worth considering when the calculation needs its additional error-handling options, but check its function number and options carefully before relying on it.

A formula that sums visible cells through a more elaborate combination of functions may be appropriate for unusual layouts, but it is rarely needed for a standard filtered list. In most everyday workbooks, the important choice is simply between SUBTOTAL’s 9 and 109 behavior and a range that accurately covers the records.

Frequently Asked Questions

Does SUBTOTAL automatically ignore filtered rows?
Yes. SUBTOTAL excludes rows filtered out of a list, whether you use function number 9 or 109. The difference between the two is whether manually hidden rows are included.

What formula sums only visible rows in Excel?
With amounts in B2:B100, use =SUBTOTAL(109,B2:B100) to exclude both filtered-out and manually hidden rows. Use function number 9 instead if manually hidden rows should remain in the sum.

Does SUM ignore filtered rows?
No. SUM includes values in its range even when their rows are hidden by a filter. Use SUBTOTAL when the answer should update to reflect the filtered records.

Will the total update when I change a filter?
Yes. A SUBTOTAL formula recalculates when the filter changes, so the result reflects the rows currently included by that filter. It can also respond when rows are hidden or unhidden, depending on the function number.

Can I sum visible rows in an Excel Table?
Yes. Use the Table’s Total Row or enter a SUBTOTAL formula that refers to the numeric table column. Table references expand with the table as records are added.

Does SUBTOTAL ignore rows hidden manually?
Function number 109 does; function number 9 does not. Both exclude rows filtered out of the list. Choose the version that matches what your report means by visible.

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.