When analysing financial statements, transaction ledgers, or sales databases in Microsoft Excel, users routinely apply AutoFilters or manually hide specific rows to examine sub-segments of data. However, standard arithmetic functions such as SUM, AVERAGE, and COUNT are completely indifferent to row visibility: even when rows are hidden from view, standard functions continue adding and averaging every hidden cell in the underlying range.
To perform accurate, dynamic calculations on visible records without distorting grand totals, Excel provides the versatile SUBTOTAL function. Beyond dynamically excluding filtered-out rows, SUBTOTAL features built-in intelligence that automatically ignores other nested SUBTOTAL formulas within its target range, completely eliminating the risk of double-counting in multi-tiered management reports. This tutorial explains how to use the SUBTOTAL function in Microsoft Excel with comprehensive examples and explains the crucial differences between function numbers 1–11 and 101–111.
SUBTOTAL Function Syntax and Arguments
The syntax for the SUBTOTAL function is straightforward:
=SUBTOTAL(function_num, ref1, [ref2], ...)
function_num(Required): A numeric code between1and11or101and111that tells Excel which mathematical calculation to perform (such as Sum, Average, Count, or Max) and whether to ignore manually hidden rows.ref1(Required): The first named range or cell range coordinate to calculate (e.g.D2:D100).[ref2], ...(Optional): Additional disjoint cell ranges or reference areas you wish to include in the aggregate calculation.
Function Number Codes: 1–11 vs 101–111 Explained
The primary key to mastering the SUBTOTAL function is selecting the appropriate function_num code. Excel provides two parallel sets of codes for the 11 supported calculation operations:
| Calculation Type | Code (1–11): Excludes AutoFiltered Rows, Includes Manually Hidden Rows | Code (101–111): Excludes AutoFiltered AND Manually Hidden Rows |
|---|---|---|
| AVERAGE | 1 |
101 |
| COUNT (Numbers only) | 2 |
102 |
| COUNTA (Non-blank cells) | 3 |
103 |
| MAX | 4 |
104 |
| MIN | 5 |
105 |
| PRODUCT | 6 |
106 |
| STDEV.S | 7 |
107 |
| STDEV.P | 8 |
108 |
| SUM | 9 |
109 |
| VAR.S | 10 |
110 |
| VAR.P | 11 |
111 |
Crucial Rule: Both sets of codes (1–11 and 101–111) automatically ignore rows that have been filtered out using Excel’s AutoFilter dropdown menus. The difference occurs only when a row is hidden manually (for example, by right-clicking a row header and selecting Hide). Codes 1–11 will include manually hidden rows; codes 101–111 will ignore both manually hidden rows and filtered rows.
Practical Example 1: Summing Only Filtered Visible Rows
Suppose you manage a sales table where column E2:E100 contains transaction revenues. When you filter the table by region (e.g. showing only “EMEA”), you want the summary card at the top of the worksheet to show total EMEA revenue:
# Calculate the sum of all visible rows in E2:E100
=SUBTOTAL(109, E2:E100)
Whenever you change the filter criteria (or manually hide individual outlier rows), the formula dynamically recalculates to reflect solely the visible values on screen.
Practical Example 2: Counting Filtered Records with COUNTA
To display an accurate count of visible records currently matching your filter criteria (for example, counting customer names in column A2:A100):
# Count all visible non-blank customer names
=SUBTOTAL(103, A2:A100)
If your table contains 100 rows total, but filtering by department displays only 14 records, =SUBTOTAL(103, A2:A100) evaluates directly to 14.
Practical Example 3: Preventing Double-Counting in Grand Totals
One of the greatest hazards in multi-level financial reporting is double-counting departmental subtotals inside the grand total calculation. With standard SUM formulas, summing an entire column that already contains intermediate subtotals produces an erroneous doubled figure.
The SUBTOTAL function automatically solves this problem: any cell within the referenced range that itself contains a SUBTOTAL formula is strictly ignored!
# North Region Subtotal in cell E15:
=SUBTOTAL(9, E2:E14)
# South Region Subtotal in cell E30:
=SUBTOTAL(9, E16:E29)
# Grand Total in cell E32:
=SUBTOTAL(9, E2:E30)
In this architecture, the Grand Total formula in cell E32 reads the entire span E2:E30, but it automatically skips the values in E15 and E30, calculating the correct, true grand sum without manual cell exclusions.
SUBTOTAL vs AGGREGATE: When to Upgrade
While SUBTOTAL is the standard function for filtered tables, Excel also provides the newer AGGREGATE function (introduced in Excel 2010):
- SUBTOTAL: Supports 11 mathematical functions, automatically ignores nested subtotals, and handles filtered or manually hidden rows. However, it cannot ignore
#N/A,#VALUE!, or#DIV/0!errors. - AGGREGATE: Supports 19 mathematical operations (including median, percentiles, and large/small rankings) and provides an options argument that can ignore both hidden rows AND formula errors (such as
#N/A). - If your dataset is clean of calculation errors,
SUBTOTALis widely preferred due to its broad cross-platform compatibility, universal adoption across all spreadsheet applications, and clean, memorable syntax.