Sum Only Visible (Filtered) Rows

All versionsSUBTOTALAGGREGATE

A normal SUM adds every row — even the ones a filter has hidden. SUBTOTAL adds only the visible rows, so your total updates to match whatever the AutoFilter is showing.

Quick formula: to sum only the rows left visible by a filter:

=SUBTOTAL(109, B2:B100)

Function number 109 means SUM-ignoring-hidden-rows; SUBTOTAL recalculates as you filter.

Functions used (tap for the full reference guide):

SUBTOTAL AGGREGATE

The example

A filter is showing only West rows; the total reflects just those.

	A	B
1	Region	Amount
2	West	$120
3	East (hidden)	$90
4	West	$200
5	Visible total:	$320

The formula

The filtered total:

=SUBTOTAL(109, B2:B4) // adds only the visible West rows → 320

How it works

The function number controls both the operation and the hidden-row behavior:

The first argument is a function code: 9 = SUM, 1 = AVERAGE, 2 = COUNT, 4 = MAX, and so on.
Codes in the 100s (109, 101…) also ignore rows hidden manually, not just by filter. Codes in the 1–11 range ignore filtered rows but keep manually hidden ones.
As you filter, SUBTOTAL recalculates to total only what’s on screen — 320 for the two West rows.
Bonus: SUBTOTAL ignores other SUBTOTALs in the range, so nested subtotals don’t double-count.

AGGREGATE goes further. =AGGREGATE(9, 5, B2:B100) sums visible rows and can ignore errors (option 6) — handy when the column has stray #N/As.

Try it: interactive demo

Live demo

Toggle which rows are “filtered out”; compare SUM vs SUBTOTAL.

West $120 East $90 West $200

SUBTOTAL (visible): SUM (all):

Variations

Average / count visible rows

Change the function code:

=SUBTOTAL(101, B2:B100) // AVERAGE =SUBTOTAL(103, B2:B100) // COUNTA

Visible rows AND ignore errors

AGGREGATE option 5 = ignore hidden; 6 = ignore errors (use 7 for both):

=AGGREGATE(9, 7, B2:B100)

A live row counter

Number only visible rows:

=SUBTOTAL(103, $B$2:B2)

Pitfalls & errors

9 vs 109. Code 9 ignores filtered rows but still counts rows you hid by right-clicking → Hide. Code 109 ignores both. Pick deliberately.

SUBTOTAL only reacts to row hiding, not to hidden columns or to IF conditions. For conditional totals, use SUMIFS.

Don’t mix data and a SUBTOTAL in the same range by accident — though SUBTOTAL conveniently skips other SUBTOTALs, a stray total cell inside the range can still confuse a reader.

Practice workbook

📊

Download the free Sum Only Visible (Filtered) Rows practice workbook

A filtered list with live SUBTOTAL sum/average/count, the AGGREGATE ignore-errors version, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum only visible rows after filtering in Excel?

Use =SUBTOTAL(109, range). Function code 109 sums while ignoring filtered and manually hidden rows, so the total matches what's on screen.

What's the difference between SUBTOTAL 9 and 109?

Code 9 ignores rows hidden by a filter but still includes rows hidden manually. Code 109 ignores both filtered and manually hidden rows.

How do I sum visible rows and ignore errors too?

Use AGGREGATE: =AGGREGATE(9, 7, range) sums while ignoring hidden rows and error values (option 7 combines both).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUBTOTAL · AGGREGATE