Sum, Count & Average

Excel formula for Subtotal that ignores filtered rows

Sum only the visible rows after filtering, unlike a plain SUM.

Copy this formula

=SUBTOTAL(109, B2:B100)

How the formula works

SUBTOTAL with function code 109 sums a range while ignoring rows hidden by a filter. Codes 1–11 ignore filtered rows; 101–111 also ignore manually hidden rows. Swap 109 for 101 (average), 102 (count), and so on.

Examples

=SUBTOTAL(101, B2:B100)→215

Average of the visible rows only.

FAQ

Why use SUBTOTAL instead of SUM?

SUM always totals every row; SUBTOTAL respects your filter, so the total matches what you see.

Related formulas

Running total (cumulative sum)Sum with multiple conditions

Functions used

SUBTOTAL SUM

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free