Skip to content

Sum only visible (filtered) rows

A total that updates when you filter the table and ignores hidden rows.

The formula

Argument separator
Excel and Google Sheets
=SUBTOTAL(109, C2:C100)

How it works

  1. 109

    Function code for SUM that skips rows hidden by a filter or by hand. Use 101 for AVERAGE, 103 for COUNTA.

  2. C2:C100

    The values to total.

Example

B: Region (filtered to West)C: Amount
West200
West50

Tips

  • Result with the filter on: 250. Without the filter, all rows count.