Sum Only Visible Cells in Excel (after filtering or hiding rows)
Filter a list and =SUM() still adds every row, hidden or not. To total only the rows you can see, use SUBTOTAL.
The formula
=SUBTOTAL(109,B2:B100)
- 9 ignores rows hidden by a filter.
- 109 also ignores rows you hid by hand (right-click › Hide). Use 109.
The fastest way to get it
Turn on a filter, click the cell under the numbers, press Alt+=. On a filtered list Excel writes SUBTOTAL(9, …) instead of SUM. See AutoSum shortcut.
Other visible-only calculations
| Need | Code |
|---|---|
| Average | =SUBTOTAL(101,B2:B100) |
| Count numbers | =SUBTOTAL(102,B2:B100) |
| Count non-empty | =SUBTOTAL(103,A2:A100) |
| Max / Min | =SUBTOTAL(104,…) / 105 |
Visible and no errors: AGGREGATE
If the column has an #N/A, SUBTOTAL fails. AGGREGATE can skip both hidden rows and errors:
=AGGREGATE(9,7,B2:B100)
9 = SUM, 7 = ignore hidden rows and errors.
Copy only the visible rows
Select the filtered range › Alt+; (selects visible cells only) › Ctrl+C › paste. Without Alt+; Excel may paste the hidden rows too. Or use Go To Special › Visible cells only.
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
