SUBTOTAL in Excel: totals that ignore filtered rows

Syntax

=SUBTOTAL(function_num, range)

The common function numbers

• 9 SUM · 1 AVERAGE · 2 COUNT (numbers) · 3 COUNTA (non-blank) · 4 MAX · 5 MIN
• Add 100 (109, 101, 103…) to also ignore rows you hid by hand.

Sum what’s visible after filtering

=SUBTOTAL(9,C2:C500)

Filter to North and the total shows North only. Sum visible cells covers more.

Count visible rows

=SUBTOTAL(103,A2:A500)

A live “showing X records” counter.

SUBTOTAL ignores other SUBTOTALs

Put a subtotal under each group and a SUBTOTAL grand total at the bottom — no double counting.

The Data › Subtotal tool

1. Sort by the column to group on (e.g. Region).
2. Data › Subtotal.
3. At each change in: Region · Use function: Sum · Add subtotal to: Sales › OK.

Excel inserts a total after every region, a grand total, and 1-2-3 outline buttons. See group rows. Remove: Data › Subtotal › Remove All.

Need to skip error cells too?

AGGREGATE: =AGGREGATE(9,7,C2:C500) ignores hidden rows and errors.