Excel SUBTOTAL Function

SUBTOTAL returns a summary value, such as a sum, average, or count, for a range of cells. It automatically excludes rows hidden by a filter, which SUM and AVERAGE never do. The catch is the function_num argument: pick a number from 1–11 and manually hidden rows still get included in the result. Pick the matching number from 101–111 and they're excluded too. Mix these up and your total will look plausible but be wrong — with no error to warn you.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and every earlier version back to Excel 5.0. The 101–111 codes are the exception — they were introduced in Excel 2003. Older workbooks or the 1–11 codes work everywhere; if you're opening a file built in Excel 2000 or earlier and see a 101–111 code fail, that's why.

Syntax

=SUBTOTAL(function_num, ref1, [ref2], ...)

Parameter	Required	Description
function_num	Yes	A code from 1–11 or 101–111 that sets which calculation runs (sum, average, count, max, and so on) and whether manually hidden rows are counted.
ref1	Yes	The range to summarize. "Ref" is just shorthand for reference — a cell range like C2:C48, a table column, or a named range, not a special data type.
ref2, ...	No	Additional ranges to fold into the same calculation. Up to 254 total.

Basic Example

You're tracking quarterly sales in a 48-row table, and a slicer hides every region except West. To total only what's currently visible:

=SUBTOTAL(9, C2:C48)

// 9      — sum function code (1–11 range, includes manually hidden rows)
// C2:C48 — the sales amount column

With no filter applied, this sums all 48 rows. Filter the table down to West and the visible row count drops to 12 — the formula recalculates instantly to reflect just those 12. Swap SUBTOTAL for a plain SUM formula and you'd get the same number either way, because SUM has no idea whether a row is hidden.

How SUBTOTAL Works

The function_num Argument Sets the Calculation and the Hidden-Row Rule

Each calculation exists twice: once as 1–11, once as the same math plus 100.

Calculation	Includes manually hidden rows	Excludes manually hidden rows
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
PRODUCT	6	106
STDEV	7	107
STDEVP	8	108
SUM	9	109
VAR	10	110
VARP	11	111

Both sets ignore rows hidden by a filter. Always. That part doesn't change.

Filtered Rows Are Always Excluded — Manually Hidden Rows Depend on the Code

Right-click a row and choose Hide, and it's "manually hidden." Apply a filter, and non-matching rows are "filtered out." Excel treats these two states differently, and SUBTOTAL is built to tell them apart. Use 9 and a manually hidden row still contributes to the total. Use 109 and it doesn't. This one distinction causes more wrong totals than any other part of the function.

SUBTOTAL Also Ignores Rows Hidden by Grouping

Collapse an outline group (the + buttons from Data > Group) and those rows behave like manually hidden rows for the purposes of SUBTOTAL. A 101–111 formula skips them. A 1–11 formula still counts them. This case rarely gets mentioned, but it trips up anyone who builds grouped reports and expects the grand total to shrink when they collapse a section.

SUBTOTAL Ignores Other SUBTOTAL Formulas Automatically

If a range fed into SUBTOTAL already contains a SUBTOTAL formula, that inner result is skipped entirely so it doesn't get counted twice. This matters most when Data > Subtotal inserts subtotal rows inside a group and then a grand total formula runs SUBTOTAL over the whole column. Without this rule, every group subtotal would get added into the grand total on top of the detail rows it summarizes — doubling the number.

Excel Tables Insert SUBTOTAL Automatically in the Total Row

Turn on the Total Row for a Table (Table Design > Total Row), and the dropdown in each column writes a SUBTOTAL formula for you — usually using the 101–111 set, so it respects filters and manual hiding both. Click the dropdown to switch from Sum to Average or Count, and Excel swaps the function_num behind the scenes. You can also type your own SUBTOTAL formula into that row if the dropdown doesn't offer the calculation you need.

Common Use Cases

Summing Only Visible Rows in a Filtered Table

An order log with 300 rows, filtered down to a single customer, still needs an accurate running total.

=SUBTOTAL(9, orders[Amount])   // sums only the rows visible after filtering

Building Grouped Subtotals with Data > Outline > Subtotal

Sort a sales list by Region first — the feature groups by whatever's already sorted, it doesn't sort for you. Then go to Data > Outline > Subtotal, set "At each change in" to Region, the function to Sum, and check the Revenue column. Excel inserts a formula like =SUBTOTAL(9, C5:C9) at each region break, plus a grand total at the bottom, all wrapped in a collapsible outline.

Counting Visible Rows That Meet a Criteria

SUBTOTAL can't take a condition on its own. Combine it with SUMPRODUCT to count only the visible rows in a 60-row support ticket log where Status equals "Open":

=SUMPRODUCT(SUBTOTAL(103, OFFSET(B4, ROW(B4:B60)-ROW(B4), 0)) * (C4:C60="Open"))

// 103              — COUNTA code, excludes manually hidden rows too
// OFFSET(...)      — builds a 1/0 array marking each row visible or hidden
// C4:C60="Open"    — the condition being matched

Out of 60 rows, this might return 17 — the count of open tickets that are also currently visible, ignoring any that a filter or manual hide has tucked away.

Handling Errors

In everyday use, SUBTOTAL rarely throws an error. Empty ranges, fully hidden ranges, and fully filtered-out ranges all just return 0 — not an error. The two errors below come from setup mistakes, not from the data itself.

Common causes of #VALUE!:

• function_num isn't one of the 22 valid codes (1–11 or 101–111)
• The calculation requires numbers but the range is entirely text

Common causes of #REF!:

• One of the referenced ranges was deleted
• A range argument points outside the worksheet's boundaries

Because both errors trace back to how the formula was built rather than the data, IFERROR is most useful here as a guard against a mistyped function_num — not as a general catch-all:

=IFERROR(SUBTOTAL(9, sales_range), "Check function_num and range")

Notes & Gotchas

Why does SUBTOTAL return #VALUE!?

The most common cause is typing a function_num outside the valid list of 22 codes, like 12 or 99. It also throws #VALUE! if you ask for AVERAGE or STDEV on a range that contains no numbers at all. Check the code against the table above and confirm the range actually has numeric data.

Why does SUBTOTAL still count a row I hid manually?

You're using a 1–11 code instead of the matching 101–111 one. The 1–11 set only excludes rows hidden by a filter — manually hidden rows still get included. Switch to 109 instead of 9, 103 instead of 3, and so on, and the manually hidden row drops out of the calculation.

Does SUBTOTAL work with PivotTable data?

Not the way it works with regular ranges. A PivotTable already calculates its own subtotals internally based on the layout you build, and those aren't standard SUBTOTAL formulas you can reference directly. To pull a specific subtotal or grand total out of a PivotTable into another cell, use GETPIVOTDATA instead — it's built for that job, and SUBTOTAL isn't.

What happens if I run SUBTOTAL on a range that includes another SUBTOTAL formula?

SUBTOTAL detects the nested formula and skips its result to avoid double-counting. This is automatic and can't be turned off. It's the reason a grand-total SUBTOTAL formula placed under several Data > Subtotal group rows returns the correct total instead of adding each group's subtotal on top of the detail rows.

Does SUBTOTAL recalculate automatically when I filter?

Yes, immediately, with no manual refresh needed. Apply or change a filter and every SUBTOTAL formula pointing at that range updates on the spot. This is the entire reason to use SUBTOTAL over SUM in a filtered report — SUM would keep showing the total for every row, filtered or not.

Related Functions

Function	Use this when...
COUNTIF	You need to count rows matching a condition, regardless of hidden state.
FILTER	You want to extract the matching rows themselves, not just a summary number.