Excel Subtotal Visible Rows: Sum Filtered Data Correctly

How-to basics · Updated November 18, 2026

To subtotal only the rows visible after filtering in Excel, use the SUBTOTAL function with function number 9 for a sum, such as =SUBTOTAL(9,B2:B100). Excel automatically ignores rows removed by a filter; use function number 109 instead if you also want to exclude rows hidden manually.

How SUBTOTAL handles visible rows

SUBTOTAL is designed to calculate results over a list or range that may be filtered. When you filter a column, Excel hides rows that do not meet the criteria. A subtotal formula skips those filtered-out rows, so its result changes as you change the filter. You do not need to add a separate condition to the formula for each filter choice.

The first argument, called the function number, tells Excel what calculation to perform. For example, 9 means SUM. Numbers 1 through 11 include rows hidden manually, while the corresponding numbers 101 through 111 exclude manually hidden rows as well. Either set skips rows excluded by a filter. This distinction matters if you use Hide on selected rows in addition to filtering.

SUBTOTAL also ignores other SUBTOTAL formulas in its referenced range. That helps prevent double-counting when a worksheet contains subtotals for separate groups and a further total for the overall list. It does not, however, automatically recognize every formula that happens to calculate a subtotal; the protection specifically applies to nested SUBTOTAL calculations.

Create a subtotal for filtered data

For a straightforward filtered total in Excel for Microsoft 365 on Windows, set up the list with a header row and keep the subtotal formula outside the data range. Then:

  1. Select a cell in the list and choose Data > Filter. Excel adds filter controls to the headers.
  2. Open the dropdown in the column you want to filter and select the values to keep. Rows that do not match are hidden by the filter.
  3. In a cell outside the range being totaled, enter a SUBTOTAL formula using function number 9 for a sum.
  4. Change the filter criteria and check that the total updates to reflect the newly visible rows.

For example, assume B2:B7 contains amounts and the category names are in A2:A7. With those amounts in the range, enter the formula below in a cell outside B2:B7. With no filter, the six amounts total 700; when a filter leaves only the Office entries visible, the result is 470.

=SUBTOTAL(9,B2:B7)

Putting the formula outside its own reference range avoids a circular reference. A separate summary cell above the list or in another column is usually convenient. If you put a formula directly beneath a range, check that it has not accidentally become part of the range you are filtering or summing.

Worked example: a total that follows the filter

Consider a small expense list with Category in column A and Amount in column B:

  • Row 2: Office, 120
  • Row 3: Travel, 80
  • Row 4: Office, 200
  • Row 5: Meals, 50
  • Row 6: Office, 150
  • Row 7: Travel, 100

The complete list totals 700: 120 + 80 + 200 + 50 + 150 + 100. Filtering Category to Office leaves three visible amounts, 120, 200, and 150, so the subtotal becomes 470. Filtering to Travel instead makes it 180. The formula stays the same; the filter determines which list rows contribute.

This is useful for sales reports, inventory lists, expense reviews, and other tables where you want to inspect one slice of the data without changing the underlying records. It also means a visible total is not necessarily the total for every record in the worksheet. If you share or print a filtered report, label the result clearly so readers know it represents the displayed rows.

Choose between function numbers 9 and 109

The most common point of confusion is the difference between filtering a row and hiding it manually. A filter hides rows because they fail the current criteria. Both SUBTOTAL function numbers 9 and 109 ignore those rows. Manually hidden rows behave differently: 9 includes them, but 109 excludes them.

Using the same expense layout, suppose the filter is set to Office and row 6, which contains 150, is also hidden manually. With amounts in B2:B7, enter the following formula outside that range. It returns 320, adding the visible Office amounts 120 and 200 while excluding the manually hidden row.

=SUBTOTAL(109,B2:B7)

With function number 9 under those same conditions, the result would remain 470 because the manually hidden 150 is included. Choose 9 when manual hiding should not change the calculation, and 109 when the total should represent only rows currently displayed, whether they were hidden by a filter or manually. If you are unsure which kind of hiding is in use, test a known row before relying on the report.

Other calculations available in SUBTOTAL

SUBTOTAL is not limited to adding visible values. Its function number can request several familiar calculations, and the 101–111 versions apply the additional rule of excluding manually hidden rows. Common choices include:

  • 1 or 101: average.
  • 2 or 102: count cells containing numbers.
  • 3 or 103: count non-empty cells.
  • 4 or 104: maximum value.
  • 5 or 105: minimum value.
  • 9 or 109: sum.

The distinction between counting numbers and counting non-empty cells is important. A count of numeric amounts will not count text labels, while a count of non-empty cells can include text. There are also function numbers for product, standard deviation, and variance. Choose the calculation that matches the question you are answering; a visible-row sum and a visible-row count describe different things.

Using an Excel table or the Subtotal command

If your data is an Excel table, its built-in Total Row is a convenient alternative to typing a formula. Select a cell in the table, open the contextual Table Design tab, and turn on Total Row. In the total row, use the cell’s dropdown to select Sum or another available calculation. Excel uses a subtotal-style calculation so the result responds to table filters. A typed formula outside the table is also useful when you want more control over the function number or where the result appears.

The separate Data > Outline > Subtotal command serves a different purpose. It inserts group-level summary rows into a sorted list, such as a subtotal after each department, and creates an outline that can collapse or expand detail rows. It is not necessary just to total the currently visible filtered rows. Excel’s Subtotal command is intended for a normal range rather than an Excel table; if it is unavailable for a table, use the Total Row or a SUBTOTAL formula instead.

Use a formula or a table Total Row when you want one total that follows filtering. Use the outline Subtotal command when you need separate summaries within categories and expandable detail. These approaches can coexist, but take care not to add group totals to an overall sum as if they were raw data. SUBTOTAL ignores nested SUBTOTAL results when the range includes them, which is one reason it is often chosen for such summaries.

Common problems and how to fix them

  • The total does not change after filtering. Confirm that the filter is applied to the rows in the formula’s range and that the formula uses SUBTOTAL, not an ordinary SUM. Also check that the formula references the intended amount column.
  • The total includes a row hidden with Hide. If you want manually hidden rows excluded, use the 101–111 family of function numbers, such as 109 for a sum. Filtering alone does not require that change.
  • The result is zero or unexpectedly small. Check that the amount cells contain numeric values rather than numbers stored as text, and verify that the selected range includes all the intended data rows. A blank or text-only visible selection can produce a different result from a numeric total.
  • The formula reports a circular reference. Move the formula outside the range it references. Avoid including the result cell in the amount range.
  • Rows disappear but the total still seems wrong. Make sure you are comparing the visible rows with the relevant calculation. For example, a sum of visible amounts is not the same as a count of visible records. Also check whether manually hidden rows should be included.
  • The Subtotal command is unavailable. If the data is formatted as an Excel table, use its Total Row or a formula. For group summaries, convert the table to a normal range if appropriate, then use the Subtotal command on the sorted range.

One further check is whether the filter includes every data row. A blank row separating records can cause Excel’s filter range to stop early, leaving later records outside the filter. Keep the list contiguous, with one header row and no fully blank separator rows, or confirm the filter covers the entire intended range.

Frequently Asked Questions

Does SUBTOTAL ignore filtered rows automatically?
Yes. SUBTOTAL skips rows excluded by an Excel filter. For a sum, use function number 9; use 109 if manually hidden rows should be excluded too.

What is the difference between SUBTOTAL 9 and 109?
Both calculate a sum and ignore rows filtered out of the list. Number 9 includes manually hidden rows, while 109 excludes them. Choose based on whether manual hiding should affect the displayed total.

Can I subtotal visible rows without using a filter?
Yes. SUBTOTAL can still calculate over a range when no filter is applied. Function numbers 101–111 also exclude manually hidden rows; with no hidden or filtered rows, the matching 9 and 109 sums are the same.

Why does my SUBTOTAL include rows I did not expect?
Check whether those rows were filtered out or hidden manually. Function numbers 1–11 include manually hidden rows, even though they skip filtered-out rows. Also verify the formula’s range and confirm it does not include unrelated records.

Can I use SUBTOTAL with an Excel table?
Yes. You can enter a SUBTOTAL formula that refers to the relevant data, or enable the table’s Total Row from Table Design. The Total Row is designed to work with table filters and offers common calculations such as Sum.

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.