Math & Trig

SUBTOTAL Function in Excel

Returns a subtotal in a list or database, with options to include or exclude hidden values.

Syntax

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

Arguments

  • function_num (required): Number 1-11 or 101-111 specifying function
  • ref1 (required): First range to subtotal
  • ref2 (optional): Additional ranges

Examples

  • =SUBTOTAL(9, A1:A100) - Sum (ignores other SUBTOTALs) - Result: Sum value
  • =SUBTOTAL(109, A1:A100) - Sum ignoring hidden rows - Result: Visible sum

Excel SUBTOTAL function documentation

  • SUBTOTAL performs aggregate operations (SUM, AVERAGE, COUNT, MAX, MIN, etc.) with options to ignore hidden rows.
  • Syntax: =SUBTOTAL(function_num, ref1, [ref2], …). Essential for filtered reports and outlined data.
  • function_num 101–111 ignore filtered/hidden rows; 1–11 include hidden values in subtotals.
  • Related: [AGGREGATE](/functions/aggregate/), [SUM](/functions/sum/), [AutoFilter totals row in Tables](/functions/subtotal/).

Common function_num codes

  • 1 / 101 — AVERAGE (101 ignores hidden rows).
  • 2 / 102 — COUNT numeric cells.
  • 9 / 109 — SUM.
  • 2 vs 102 — use 109 for SUM on filtered sales reports.
  • Always use 101–111 range when building dashboards on filtered Excel Tables.

Step-by-step: SUBTOTAL on a filtered table

  • Step 1 — Convert data to Table (Ctrl+T) and turn on filters.
  • Step 2 — Below Amount column: =SUBTOTAL(109, Table[Amount]).
  • Step 3 — Filter Region to East — SUBTOTAL updates; plain SUM would not.
  • Step 4 — Label the cell "Visible total" so auditors know the behavior.
  • Step 5 — Avoid nesting SUBTOTAL inside SUBTOTAL — use [AGGREGATE](/functions/aggregate/) instead.

SUBTOTAL vs SUM vs AGGREGATE

  • SUM — always includes every numeric cell in ref, hidden or not.
  • SUBTOTAL — ignores rows hidden by Filter or Outline (with 101+ codes).
  • AGGREGATE — also ignores error values; more function options; safer on messy data.
  • Do not use SUBTOTAL rows inside the same SUBTOTAL reference — double-count risk.

Worked examples to copy

  • Visible sum: =SUBTOTAL(109, B2:B500).
  • Visible average: =SUBTOTAL(101, C2:C500).
  • Visible count: =SUBTOTAL(103, D2:D500).
  • Total row in Table: Excel may insert =SUBTOTAL(109,[Column]) automatically.

People also ask

  • Why SUBTOTAL after filter? — It recalculates using only visible rows with 101–111 codes.
  • SUBTOTAL vs SUM in total row? — SUBTOTAL is correct for filtered data; SUM is not.
  • Can SUBTOTAL ignore errors? — No. Use AGGREGATE for #N/A or #DIV/0! exclusion.
  • SUBTOTAL function_num 9 vs 109? — 109 ignores hidden rows; 9 does not.

Common errors

  • Function numbers: 1=AVERAGE, 2=COUNT, 9=SUM, etc.

Use cases

  • Filtered data totals
  • Grouped reports
  • Dynamic summaries

Frequently asked questions

  • What are the function numbers for SUBTOTAL? 1-11 include hidden rows, 101-111 ignore hidden rows. Key numbers: 1/101=AVERAGE, 2/102=COUNT, 3/103=COUNTA, 4/104=MAX, 5/105=MIN, 9/109=SUM, 11/111=VAR. Use 100+ series for filtered data.
  • Why use SUBTOTAL instead of SUM for filtered data? SUBTOTAL(109,...) only sums visible cells when you filter data. Regular SUM includes hidden rows. Also, SUBTOTAL ignores other SUBTOTAL cells, preventing double-counting in grouped reports with subtotals.
  • What is the difference between SUBTOTAL and AGGREGATE? AGGREGATE is more powerful: it has 19 functions (vs 11), can ignore error values, and has more options. Use SUBTOTAL for basic filtered totals, AGGREGATE when you need to ignore errors or use functions like MEDIAN.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SUBTOTAL

  • Filtered data totals — common Math & Trig scenario for SUBTOTAL.
  • Grouped reports — common Math & Trig scenario for SUBTOTAL.
  • Dynamic summaries — common Math & Trig scenario for SUBTOTAL.

SUBTOTAL in the Math & Trig category

  • Browse all Math & Trig functions at /categories/math-trig/ for related formulas.
  • SUBTOTAL syntax: =SUBTOTAL(function_num, ref1, [ref2], ...)
  • function_num (required): Number 1-11 or 101-111 specifying function
  • ref1 (required): First range to subtotal
  • ref2 (optional): Additional ranges
  • Confirm SUBTOTAL arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SUBTOTAL arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • SUM (/functions/sum/): Adds all the numbers in a range of cells.
  • AGGREGATE (/functions/aggregate/): Returns an aggregate calculation with options to ignore errors and hidden rows.
  • AVERAGE (/functions/average/): Returns the average (arithmetic mean) of the arguments.

Common problems and fixes

  • Why Is My Pivot Table Not Updating? (/problems/pivot-table-not-updating/): Refresh the Pivot Table, expand the source range or convert it to an Excel Table, then rebuild if fields were renamed.

Errors to watch for

  • Function numbers: 1=AVERAGE, 2=COUNT, 9=SUM, etc. — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SUBTOTAL on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SUBTOTAL worked examples to copy

  • =SUBTOTAL(9, A1:A100) — Sum (ignores other SUBTOTALs). Expected result: Sum value.
  • =SUBTOTAL(109, A1:A100) — Sum ignoring hidden rows. Expected result: Visible sum.

SUBTOTAL reference summary for crawlers and offline review

  • SUBTOTAL belongs to the Math & Trig category in Excel. Returns a subtotal in a list or database, with options to include or exclude hidden values.
  • Full syntax: =SUBTOTAL(function_num, ref1, [ref2], ...). Open /functions/subtotal/ for parameters, FAQs, and related pages.
  • Common mistakes: Function numbers: 1=AVERAGE, 2=COUNT, 9=SUM, etc.
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.