AI Excel Generator
← ALL GUIDES

How to Sum Only Visible Cells in Excel

Updated November 4, 2026 · AI Excel Generator

To sum only the rows currently visible in an Excel list, use SUBTOTAL with its SUM function number.

The choice of function number matters: one version respects filters, while another also skips rows you hide manually.

Use SUBTOTAL to sum visible cells

For a range such as B2:B10, enter this formula in a cell outside the range:

=SUBTOTAL(9,B2:B10)

Function number 9 tells SUBTOTAL to sum the values.

Excel excludes rows removed by a filter from the result.

It also ignores other SUBTOTAL formulas in the referenced range, which helps prevent double-counting when you have subtotals in a list.

If you want to exclude rows hidden manually as well as rows filtered out, use function number 109:

=SUBTOTAL(109,B2:B10)

This distinction is useful when you hide selected rows with the row headings’ context menu or the Home tab’s Format commands.

A filtered-out row is excluded by both formulas; a manually hidden row is excluded by 109, but included by 9.

Worked example: a filtered sales list

Suppose cells B2:B6 contain sales amounts of 120, 80, 200, 50, and 150.

With all five rows visible, the sum is 600.

If you filter the list so only the first, third, and fifth records remain visible, both formulas return 470:

  • =SUBTOTAL(9,B2:B6) returns 470.
  • =SUBTOTAL(109,B2:B6) also returns 470.

If you then manually hide the row containing 200, the first formula still includes it, while the second excludes it.

The results become 470 for function number 9 and 270 for function number 109.

The formulas recalculate as filter or row visibility changes.

Which function number should you use?

Pick the function number based on how rows become hidden:

  • Use 9 when you want to exclude filtered-out rows but include manually hidden rows.
  • Use 109 when you want to exclude both filtered-out and manually hidden rows.

The same pattern works with other calculations.

For example, SUBTOTAL(1,range) calculates an average, and SUBTOTAL(2,range) counts numeric cells.

The corresponding function numbers with 100 added, such as 101 for average or 102 for count, also ignore manually hidden rows.

Use AGGREGATE for more control

In Excel versions that support it, AGGREGATE can sum while ignoring filtered rows and, depending on the options you choose, hidden rows or errors.

For example:

=AGGREGATE(9,5,B2:B10)

Here, 9 selects SUM and option 5 ignores hidden rows.

To ignore hidden rows and errors, use option 7:

=AGGREGATE(9,7,B2:B10)

AGGREGATE is available in Excel 2010 and later, including Microsoft 365.

Its options are handy when the range may contain errors, but for an ordinary filtered total, SUBTOTAL is usually simpler.

Check Excel’s function help if you use an array form of AGGREGATE, since the supported options depend on the function’s form.

Make the formula easier to maintain

If your data is an Excel Table, use a structured reference so the formula follows the table as rows are added.

For a table named Sales with a Amount column, enter:

=SUBTOTAL(109,Sales[Amount])

Place the formula outside the table’s Amount data cells.

Tables also make it easy to filter records from the column headers, and the subtotal updates to reflect the visible records.

For a standard range, use a range that covers the intended data but not the cell containing the formula.

For example, put =SUBTOTAL(109,B2:B100) in B101.

Avoid including the result cell in its own range, and avoid overlapping ranges that could count records more than once.

Common reasons the total looks wrong

  • Rows were manually hidden: Switch from function number 9 to 109 if those rows should be excluded.
  • The formula is inside its own range: Move it outside the data range or adjust the references.
  • Some amounts are stored as text: Text values are not added as numbers.

    Check for imported values with leading apostrophes, spaces, or inconsistent decimal separators.
  • The range misses new records: Expand the reference or convert the data to an Excel Table.
  • The data is grouped or contains subtotals: SUBTOTAL ignores nested SUBTOTAL results, but verify that your ranges cover the detail rows you intend to total.
  • Rows are hidden by a custom view or outline: Confirm whether Excel treats the rows as hidden and use 109 when manually hidden rows must be omitted.

Compatibility and Google Sheets

SUBTOTAL is supported in modern desktop Excel, Excel for the web, and Google Sheets.

The core formulas =SUBTOTAL(9,B2:B10) and =SUBTOTAL(109,B2:B10) are suitable when you need a visible-row sum.

Google Sheets also excludes filtered-out rows; check the result after hiding rows manually, because visibility behavior can vary by spreadsheet application and the way rows were hidden.

AGGREGATE is an Excel function and is not a direct substitute to assume in Google Sheets.

For a worksheet that needs to work in both products, prefer SUBTOTAL and confirm behavior in the target application.

If your Excel uses semicolons as argument separators due to regional settings, write the same formula as =SUBTOTAL(109;B2:B10).

Quick formula guide

  • Filtered rows excluded; manually hidden rows included: =SUBTOTAL(9,range)
  • Filtered and manually hidden rows excluded: =SUBTOTAL(109,range)
  • Ignore hidden rows and errors in supported Excel: =AGGREGATE(9,7,range)