Sum and calculation · Short guide

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?

  1. Specify whether the total should show all data or just the filter result.
  2. For amounts in a vertical list, adapt the =SUBTOTAL(109,B2:B20) example to your range.
  3. 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)

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