The Problem: Why Standard =SUM() Fails on Filtered Data
Imagine you have a sales report with 1,000 rows. You apply a filter to view only "Electronics" in the "North" region.
Fails on Filtered Data
Standard =SUM() ignores your table filters and continues adding up all 1,000 rows in the background — giving an inflated, incorrect total!
Dynamically Recalculates
=SUBTOTAL() detects active table filters and calculates ONLY the visible rows on your screen! Every filter change updates the total live.
Formula Syntax & Function Codes
=SUBTOTAL(function_num, ref1, [ref2], ...)The first argument (function_num) tells Excel which calculation type to perform (SUM, AVERAGE, COUNT, etc.).
Calculates the mean of visible numerical values
Counts visible cells containing numbers
Counts visible non-empty cells (text & numbers)
Finds the maximum value among visible cells
Finds the minimum value among visible cells
Adds up all visible numbers in the range (Most Popular!)
Codes 1–11 (e.g. 9 = SUM)Ignores rows hidden by Table Filters, but INCLUDES rows that were manually hidden using right-click → Hide.
Codes 101–111 (e.g. 109 = SUM)Ignores BOTH Filtered-out rows AND Manually hidden rows! Best for absolute clean totals.
Interactive Filter Table & Live Counters
Filter the sales table below or toggle manual row hiding. Observe how =SUM() stays stuck while =SUBTOTAL() updates live!
| Row # | Item Name | Category | Region | Sales Amount | Status |
|---|---|---|---|---|---|
| Row 2 | MacBook Pro | Electronics | North | $1800 | 👁️ Visible |
| Row 3 | Ergonomic Desk | Furniture | South | $450 | 👁️ Visible |
| Row 4 | Wireless Headphones | Electronics | East | $200 | 👁️ Visible |
| Row 5 | Winter Hoodie | Clothing | North | $120 | 👁️ Visible |
| Row 6 | 4K Gaming Monitor | Electronics | West | $650 | 👁️ Visible |
| Row 7 | Office Chair | Furniture | East | $300 | 👁️ Visible |
| Row 8 | Running Shoes | Clothing | South | $150 | 👁️ Visible |
| Row 9 | iPhone 15 | Electronics | North | $1100 | 👁️ Visible |
3 Superpowers of the SUBTOTAL Function
1. Auto-Ignores Filtered Rows
Calculates only what is visible on screen. Ideal for interactive executive dashboards.
2. Prevents Double Counting
Automatically skips other SUBTOTAL formulas inside the range! No more accidental doubled totals.
3. Works with Excel Tables (Ctrl+T)
When you enable the Total Row in Excel Tables, Excel automatically uses SUBTOTAL under the hood!