Quick Navigation

SUBTOTAL Function

Excel 2007+

Summary

The Excel SUBTOTAL function performs aggregate calculations on filtered lists or databases, intelligently ignoring filtered-out rows and optionally excluding manually hidden rows. Perfect for dynamic summary tables that update automatically when filters are applied.

Syntax

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

Parameters

Parameter	Type	Required	Description
function_num	`Number`	Yes	Numeric code (1-11 or 101-111) selecting the function type: 1=AVERAGE, 2=COUNT, 3=COUNTA, etc.
ref1	`Range`	Yes	Required first range for subtotal calculation
[ref2],...	`Range`	No	Optional additional ranges for multi-range subtotals

Using the SUBTOTAL Function

SUBTOTAL shines in filtered data scenarios. Place it at the bottom of your data table, apply filters, and it automatically recalculates only visible cells. Use 1-11 codes to include manually hidden rows, or 101-111 to exclude them entirely. Nested SUBTOTAL functions are automatically ignored to prevent double counting.

Common SUBTOTAL Examples

Basic Sum with Filters

=SUBTOTAL(9, B2:B100)

Sums only visible cells in B2:B100 after filtering (ignores filtered rows)

Average Ignoring Hidden Rows

=SUBTOTAL(101, C2:C50)

Calculates average of visible cells only, excluding manually hidden rows

Count Non-Empty Filtered Cells

=SUBTOTAL(103, A2:A200)

Counts non-empty cells in filtered range (equivalent to COUNTA on visible data)

Multi-Range Maximum

=SUBTOTAL(104, SalesRange, BonusRange)

Finds maximum value across multiple named ranges, ignoring filtered rows

Frequently Asked Questions

Numbers 1-11 include manually hidden rows in calculations; 101-111 exclude them. Both ignore filtered-out rows.

SUBTOTAL is designed for vertical data. Horizontal subtotals won't respond to column hiding as expected.

SUBTOTAL automatically adjusts for filtered data, while regular functions include all rows regardless of filter state.

Common Errors and Solutions

#VALUE!

Cause: Using 3-D references or invalid function_num

Solution: Use standard 2D ranges and valid function codes (1-11 or 101-111)

Double counting in subtotals

Cause: Nested SUBTOTAL functions within the reference range

Solution: SUBTOTAL automatically ignores nested subtotals - no action needed

Wrong results with horizontal data

Cause: Using SUBTOTAL on row ranges instead of column ranges

Solution: Restructure data vertically or use regular aggregate functions

Notes

Function codes: 1/101=AVERAGE, 2/102=COUNT, 3/103=COUNTA, 4/104=MAX, 5/105=MIN, 6/106=PRODUCT, 7/107=STDEV, 8/108=STDEVP, 9/109=SUM, 10/110=VAR, 11/111=VARP
Always excludes filtered rows regardless of function_num
Perfect companion to Excel's AutoFilter and Table features
Use with structured tables for best results

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+