How to Sum in Excel Without Hidden Rows Using SUBTOTAL

How-to basics · Updated November 20, 2026

To total only visible values in Excel for Microsoft 365 on Windows, use SUBTOTAL with function number 109. If the numbers are in B2:B20, enter =SUBTOTAL(109,B2:B20) in an empty cell. The result excludes rows hidden manually or by a filter, and updates as the visible rows change.

This is more reliable than SUM when you want a total that reflects what is currently displayed. You can use the same formula with an ordinary range or a column of an Excel table. The steps below explain how to set it up, check the result, and fix common issues.

What You’ll Need

  • A worksheet containing the values you want to add, with the data arranged in a single column or a clearly defined range.
  • An empty cell outside the range for the total. If you put the total inside the range it sums, you can create a circular reference.
  • At least one hidden row if you want to verify that the formula excludes it. You can hide a row temporarily and unhide it after checking.

No add-in or special setting is required. SUBTOTAL is available in current desktop Excel and Excel for the web. The formula syntax is the same on Windows and Mac.

Step-by-Step

  1. Identify the range to add. Find the first and last cells containing the values, and note the column letter and row numbers. For example, if the amounts are in B2 through B6, the range is B2:B6. Include only the rows you want considered; avoid including a separate grand total cell, since that could double-count values.

    Check that the entries are numbers, not text that merely looks like numbers. A cell formatted as Currency or Number is fine; formatting changes how a number looks, not whether Excel can add it. If values are stored as text, convert them before relying on the total.

  2. Choose an empty cell for the result. Click a cell below or beside the data, but outside the range. If a filter is active, make sure the total cell is not itself in a row that will be filtered out. A total above the data is often convenient when a filter could hide rows below it.

  3. Enter the visible-row version of SUBTOTAL. With values in B2:B6 and the answer cell outside that range, type =SUBTOTAL(109,B2:B6) and press Enter. The first argument, 109, tells Excel to perform a sum while ignoring manually hidden rows. The second argument is the range to total.

    For a quick check, suppose B2:B6 contains 10, 20, 30, 40, and 50. If the row containing 30 is hidden, the formula returns 120: 10 + 20 + 40 + 50. If all five rows are visible, it returns 150.

  4. Hide a row or apply a filter. To hide a row manually, right-click its row number and choose Hide. To filter a list, select a cell in the data and choose Data > Filter, then use a column’s filter arrow to select the records to display. SUBTOTAL excludes rows removed from view by a filter, whether you use 9 or 109. The 109 version also excludes rows hidden manually.

    When a filter is already active, the formula responds to the records shown. You do not need to edit the range each time you change the filter. If your data has a header row, do not include the header in the sum range.

  5. Check that the total changes as expected. Hide one row that contains a known value, or filter out a record with a known amount. The displayed total should drop by exactly that amount. Clear the filter or unhide the row, and confirm that the value is included again.

    For instance, if the visible total was 150 and you hide the row containing 30, the expected total is 120. This simple check helps catch an incorrect range or a total cell that was accidentally included in the calculation.

  6. Use the formula for a longer range when needed. If your values occupy B2:B500, use that full range rather than selecting only the rows currently visible. With values in B2:B500 and the result cell outside that range, enter =SUBTOTAL(109,B2:B500). The formula considers the range as rows are hidden, shown, or filtered.

    If you add new records later, confirm that they fall inside the range. A regular cell range does not always expand automatically when you type below it. You can edit the formula’s ending row, or convert the data to an Excel table so added records are included in its column.

  7. Optionally use a table for expanding data. Click inside the dataset, choose Insert > Table, confirm that the range and header setting are correct, and select OK. When you add rows to the table, its columns expand with the data. You can enter a SUBTOTAL formula in a cell outside the table, or use a table column reference.

    For a table named Sales with a numeric Amount column, the formula is =SUBTOTAL(109,Sales[Amount]). This assumes the table is named Sales and the column header is Amount; table names and headers must match your workbook. Check Table Design if you need to confirm or change the table name.

Common Mistakes

  • Using SUM. SUM adds values in hidden rows as well as visible ones. If you need the answer to change with row visibility, replace SUM with SUBTOTAL using 109. SUM remains suitable when hidden values should still count.

  • Using 9 instead of 109 for manually hidden rows. Both SUBTOTAL(9,range) and SUBTOTAL(109,range) ignore rows excluded by a filter. But 9 includes rows hidden manually, while 109 ignores them. Choose 109 when both kinds of hidden rows should be left out.

  • Putting the total inside its own range. A formula that includes its own cell can cause a circular reference or an incorrect result. Keep the result cell outside the range being added, and check the range endpoints if Excel displays a warning.

  • Leaving out new rows. If you add data below a fixed range such as B2:B20, that new data may not be included. Extend the range or use a table with a column reference. Do not assume that the formula expands simply because a new value is adjacent to it.

  • Expecting hidden columns to be excluded. The 109 option is for hidden rows, not hidden columns. Hiding a column does not make its values disappear from a SUBTOTAL range. If your data spans multiple columns, define the range carefully and use a formula suited to the values you intend to add.

Tips

  • Use 109 as the default when your goal is “sum only what I can see.” It covers manual hiding and filtering, so you do not have to change the formula when you switch from one method to the other.

  • Keep the data in one continuous list with one header row and no blank rows in the middle. This makes it easier to apply filters and less likely that you will omit records when setting the range.

  • Use a clear label such as Visible total beside the result. That tells other people why this number may differ from a total calculated with SUM, which includes hidden rows.

  • If you want to compare the visible total with the total of every record, put the two calculations in separate cells and label them clearly. With all values in B2:B20, a SUM formula in a cell outside that range calculates the all-row total; remember that it will still include hidden records.

  • When checking a formula, select its cell and inspect the formula bar to confirm the function number and range. For a quick audit, temporarily unhide all rows and compare the visible total with the expected sum of the full list.

Troubleshooting

  • The total still includes a manually hidden row. Check that the formula begins with SUBTOTAL and uses 109, not 9 or SUM. Also confirm that the row is actually hidden; filtering a record out and manually hiding a row are different actions, though 109 handles both.

  • The result does not change after filtering. Check that the filter is applied to the rows included in the formula and that the formula’s range covers the data. Clear the filter, verify the source values, then apply a filter that removes a record with a known amount. If the workbook uses manual calculation, choose Formulas > Calculation Options > Automatic, or press F9 to recalculate.

  • The result is zero or lower than expected. Check for numbers stored as text, an incorrect range, or a filter that leaves no numeric records visible. Click a suspicious value and look for a leading apostrophe or text alignment that suggests it is stored as text. Convert text-formatted numbers to numeric values, then check the total again.

  • Excel shows an error instead of a number. Inspect the cells in the range for errors such as #VALUE! or #N/A. SUBTOTAL cannot produce a normal sum if an included cell contains an error. Correct the source error if appropriate, and verify that the formula’s parentheses and range are intact.

  • The total counts a row that seems hidden. Check whether the row is hidden inside a different range or whether the value you are seeing is in a separate column outside the formula’s range. Also check for duplicate records or a subtotal value that has been included as ordinary data. SUBTOTAL ignores nested SUBTOTAL results in a referenced range, but it cannot identify every manually entered total.

Frequently Asked Questions

Does SUBTOTAL 109 ignore rows hidden by a filter?

Yes. SUBTOTAL excludes filtered-out rows with either function number 9 or 109. The difference is that 109 also ignores rows hidden manually, so it is the better choice when both types should be omitted.

What is the difference between SUBTOTAL 9 and SUBTOTAL 109?

Both function numbers calculate a sum and skip filtered-out rows. Number 9 includes manually hidden rows; number 109 excludes them. If your requirement is to total visible rows only, use 109.

Will the formula update when I hide or unhide a row?

SUBTOTAL is designed to respond to changes in which rows are visible. After hiding or unhiding a row, check that the workbook is set to automatic calculation if the displayed result appears stale. Press F9 on Windows to recalculate; on Mac, use Fn+F9 on keyboards where function keys control system features.

Can I use SUBTOTAL with an Excel table?

Yes. Use the table’s column reference in the formula, making sure the table name and header are exact. A table reference can make the range easier to maintain as you add records, while SUBTOTAL still responds to filtered rows.

Does SUBTOTAL 109 ignore hidden columns too?

No. The 109 option ignores hidden rows, not hidden columns. If you hide a column containing values, those values can still be included when they are part of the referenced range.

Should I use SUBTOTAL or SUM for a normal total?

Use SUM if hidden and filtered-out records should remain in the total. Use SUBTOTAL with 109 if you want the calculation to reflect visible rows. Choose based on whether the total is meant to describe the whole dataset or only the currently displayed records.

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.