SUBTOTAL Function

Calculates a total, average, count and more — ignoring other subtotals.

Category

Math & Trig

Available in

All versions, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

SUBTOTAL runs one of 11 calculations (SUM, AVERAGE, COUNT, MAX…) chosen by a number. Its superpower: it skips any other SUBTOTAL inside the range, so section subtotals and a grand total can live in the same column.

Numbers 1–11 include rows you've hidden by hand; 101–111 leave them out. Rows hidden by a filter are always left out — which is why Table total rows use SUBTOTAL(109, …).

Syntax

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

Arguments

ArgumentRequired?What it does
function_numYes1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEVP, 9 SUM, 10 VAR, 11 VARP. Add 100 to skip manually hidden rows.
ref1YesThe range to calculate.
ref2, …OptionalMore ranges.

Examples

Example 1 — Section totals and a grand total

Each region has a subtotal; the grand total ignores them automatically.

B7fx=SUBTOTAL(9, B2:B6)
AB
1RepSales
2Ana (West)620
3Cara (West)500
4West subtotal1120
5Ben (East)480
6East subtotal480
7Grand total1600
=SUBTOTAL(9, B2:B6) Explain

A plain SUM of B2:B6 would double-count and give 3,200.

Example 2 — Totals that follow a filter

Filter the list by region and the total updates to show only the visible rows.

=SUBTOTAL(109, C2:C200) Explain

Common mistakes

  • Errors stop it. A #N/A anywhere in the range breaks SUBTOTAL. AGGREGATE can skip errors.

Notes

  • Only works down columns: hidden columns are never skipped.
  • Related functions: AGGREGATE, SUM.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.