Excel SUM Without Hidden Cells: Which Formula to Use

To sum only visible rows in Excel, use SUBTOTAL with function number 109. With amounts in B2:B6 and rows 3 and 5 hidden, =SUBTOTAL(109,B2:B6) returns 190 when the visible values are 120, 50, and 20. It excludes rows hidden manually and rows removed by a filter; ordinary SUM does not.

What “hidden cells” means in Excel

People often say “hidden cells” when they mean entire worksheet rows that are hidden. A row can be hidden manually, such as when you select a row, right-click, and choose Hide, or it can disappear because a filter excludes it. For a column of figures, these are the cases where a visible-row total is usually needed.

Hiding a row changes what you see, not the value stored in its cells. A regular sum still includes those values. This is useful when the hidden data should remain part of the calculation, but it causes a misleading result when you want a subtotal of the rows currently on display.

Other visual changes do not make a cell hidden for calculation purposes. For example, white font, a custom number format that displays a blank, or a conditional formatting rule can make a value hard to see, but the value remains part of a sum. If you need to exclude those entries, use a condition or change the data rather than relying on their appearance.

Use SUBTOTAL to sum visible rows

The function number 109 tells SUBTOTAL to perform a sum while ignoring rows hidden manually. SUBTOTAL also ignores rows excluded by a filter, so this is the practical choice when a total should adjust to the rows currently visible. It is designed for lists and vertical ranges, such as a sales column.

With values 120, 80, 50, 30, and 20 in B2:B6, and rows 3 and 5 manually hidden, =SUBTOTAL(109,B2:B6) returns 190: 120 + 50 + 20. Unhide either row and the total updates to include its value. Apply a filter that hides records and the total updates to include only the rows still displayed.

The first argument is a number that selects the calculation. For a visible-row sum, use 109 rather than 9. The second argument is the range to calculate. You can select the range with the mouse while entering the formula, or type its address. The function returns one total; it does not delete, alter, or permanently exclude hidden records.

Enter the formula and check the result

Place the amounts in one column, with a header above the data if appropriate. For this example, the amounts are in B2:B6.
Click the cell where the total should appear, preferably below the list but outside the range being summed.
Enter =SUBTOTAL(109,B2:B6) and press Enter. If rows 3 and 5 are hidden and the five values are 120, 80, 50, 30, and 20, the result is 190.
Hide or unhide a data row, or change a filter, and check that the total changes to reflect the displayed records.

Keeping the result cell outside the input range avoids accidental self-reference. If the list is likely to grow, consider converting it to an Excel Table so that new records can be incorporated more conveniently. The basic method works in Excel for Microsoft 365 on Windows and Mac, and in Excel for the web; the ribbon layout can differ slightly between platforms.

Filtered rows and manually hidden rows are different

Both kinds of hidden row disappear from view, but Excel gives you a choice about manually hidden rows. SUBTOTAL with function number 9 excludes rows removed by a filter but includes rows hidden manually. Function number 109 excludes both. That difference matters if someone hides a record temporarily but still expects it to count in a total.

With values 120, 80, 50, 30, and 20 in B2:B6, if rows 3 and 5 are hidden manually, =SUBTOTAL(9,B2:B6) returns 300 because it includes all five values. In the same layout, =SUBTOTAL(109,B2:B6) returns 190. If a filter instead hides the rows containing 80 and 30, both function numbers return 190 because filtered-out rows are excluded either way.

For a filterable report, 109 is generally the safer choice if the intended meaning is “sum what is visible.” If manual hiding is just a display convenience and those records must remain in the total, 9 may be the right choice. Choose based on what the total is meant to communicate, not simply on which rows happen to be hidden today.

A small example: visible total versus full total

Suppose a weekly expense list has these entries: office supplies, 120; travel, 80; software, 50; meals, 30; and postage, 20. All five records add to 300. If travel and meals are hidden because you are reviewing the remaining categories, a visible-only total is 190. This is the result the 109 version returns for that range.

By contrast, a standard sum still returns the full 300. With those five amounts in B2:B6, =SUM(B2:B6) returns 300 whether rows 3 and 5 are hidden manually or excluded by a filter. That is not a formula error: SUM is doing exactly what it is designed to do, adding the values in the referenced range without regard to row visibility.

This distinction is useful when comparing a filtered view with an all-record total. A visible-only total answers, “What do the records currently shown add up to?” A standard sum answers, “What do all values in this range add up to?” Both can be valid; they answer different questions. Label the result clearly if other people will use the worksheet.

SUBTOTAL compared with SUM and AGGREGATE

Use SUM when every value in the range should count, even if some rows are out of sight. Use SUBTOTAL when filtering or hiding rows should affect the result. One additional benefit of SUBTOTAL is that it ignores other SUBTOTAL formulas inside its reference range. This helps prevent a grand total from counting an existing subtotal twice.

AGGREGATE is another option when a calculation needs to ignore certain errors as well as hidden rows. For example, with 120 in B2, 80 in B3, an #N/A error in B4, 30 in B5, and 20 in B6, and with rows 3 and 5 manually hidden, =AGGREGATE(9,7,B2:B6) returns 140. The first argument selects Sum, and option 7 tells the function to ignore hidden rows and error values. This is more specialized than the usual visible-row total; use SUBTOTAL when you do not need that error-handling behavior.

When deciding between these functions, consider what should happen to errors, manual hiding, and filters. A formula that ignores an error may produce a number even though some source data needs attention. That can be helpful in a deliberate report, but it can also conceal a problem. Make the treatment of errors part of the calculation decision.

Common problems and ways to diagnose them

The total has not changed after hiding rows. Check whether the formula uses SUM or SUBTOTAL with function number 9. For a total that ignores manually hidden rows, use 109.
The result is larger than expected. Verify that the range does not include extra amounts, a prior total, or rows that should remain included. Check whether the rows were hidden manually or filtered out, since 9 treats those cases differently.
The result is smaller after filtering. That is expected for SUBTOTAL: filtered-out rows do not contribute. Clear the filter if you need to review the full-list total.
A displayed blank still affects the result. A formula returning an empty string or a number format that hides a value is not the same as hiding its worksheet row. Check the source values and the formula’s criteria.
The formula returns an error. Check the range for errors in its source data and confirm that the range reference is valid. SUBTOTAL is not a general-purpose error-ignoring sum; consider AGGREGATE only if ignoring errors is intended.

To reveal rows hidden manually in Excel for Windows, select the rows above and below the hidden area, then choose Home > Format > Hide & Unhide > Unhide Rows. A filter-hidden row is different: use the filter drop-down to include the excluded item or clear the filter. Unhiding rows will not restore records excluded by a filter.

Working with filters and Excel Tables

To filter a simple list in Excel for Windows, select a cell in the list and use Data > Filter. The filter arrows let you choose which records to display. You can also toggle filters with Ctrl+Shift+L on Windows. On Mac, use Data > Filter if the shortcut is not available or differs in your Excel configuration.

Excel Tables are useful for data that grows over time. Select the data and choose Insert > Table, making sure the header setting matches the list. When the table is selected, the Table Design tab provides a Total Row option. A total row uses subtotal-style behavior for its column calculation, so it can update as table filters change. The drop-down in the total cell lets you choose a calculation such as Sum.

A table can make formulas easier to maintain because its range expands as you add records, but check the result after changing filters or table settings. If you type a separate formula outside the table, make sure its reference includes all the data rows you intend to total. The built-in Total Row is a convenient alternative when the total belongs directly with the table.

Frequently Asked Questions

Does SUBTOTAL ignore rows hidden by a filter?
Yes. Both function numbers 9 and 109 exclude rows removed by a filter. The difference is that 109 also ignores rows hidden manually, while 9 includes those manually hidden rows.

Does SUM ignore hidden rows in Excel?
No. SUM adds values in its referenced range whether their rows are visible, manually hidden, or filtered out. Use SUBTOTAL when the total should follow the visible rows.

Can I sum visible cells in only one column?
Yes. Put the column’s data range in the SUBTOTAL formula. The function calculates the visible values in that range; it does not require you to select every column in the worksheet.

Will SUBTOTAL ignore a hidden column?
SUBTOTAL is intended to respond to hidden rows and filtered records, not to exclude a value merely because its column is hidden. If you need to exclude a column’s data, change the range or use a formula designed for that specific selection.

Why does a cell that looks blank still count?
A cell can look blank because of formatting or because a formula displays an empty string, while still containing a value or formula. Row visibility and cell appearance are separate. Inspect the underlying cell contents or use criteria that explicitly select the records you want.

Can I use this in Excel for Mac or the web?
Yes. SUBTOTAL is available in Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The interface and shortcut availability can differ, but the function’s basic behavior is the same.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.