How to sum only visible rows in Excel?
Braxorn · Last updated 2026-11-22 · English
Applying a filter does not ensure that the SUM formula automatically sums only visible rows. If you want a total that changes according to the filter, you can use SUBTOTAL on a vertical data range.
What can you do?
- Specify whether the total should show all data or just the filter result.
- For amounts in a vertical list, adapt the =SUBTOTAL(109,B2:B20) example to your range.
- Change the filter and check the total; also decide whether to include manually hidden rows according to the business rule.
A small example
Exclude row B3 with the filter when B2=10, B3=20, B4=30. The sum of the amounts visible with SUBTOTAL must be 40.
What to watch for
109 also excludes manually hidden rows; 9 includes these. Both exclude rows that were left out by the filter. Do not assume the same behavior for hidden columns.
Check the result
Verify that the total includes all appropriate rows again when you remove the filter.
Examples are synthetic. Formulas use English function names and comma separators; names or separators may differ with your Excel language and regional settings. Menus and features vary by version.
Microsoft source (English)
Review your own workbook
You can start reviewing your Excel file in Braxorn. Check which controls are supported and review incomplete checks alongside the report scope.
Review your file