How to use the Excel SUBTOTAL function

Summary

The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.

Purpose

Get a subtotal in a list or database

Return value

A number representing a specific kind of subtotal

Syntax

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

Arguments

function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
ref1 - A named range or reference to subtotal.
ref2 - [optional] A named range or reference to subtotal.

Usage notes

Use the SUBTOTAL function to get a subtotal in a list or database. SUBTOTAL has the ability to use a variety of functions when subtotaling, including AVERAGE, COUNT, MAX, and others (see table below for a complete list). By default, SUBTOTAL excludes values in rows hidden by a filter, as explained below. This makes SUBTOTAL very useful in Excel Tables.

Below are a few examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range. Notice the only difference is the value used for the function_num argument:

=SUBTOTAL(109,range) // SUM
=SUBTOTAL(103,range) // COUNT
=SUBTOTAL(101,range) // AVERAGE

Available functions

SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. The table below shows available functions:

Function	Include hidden	Ignore hidden
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

Manually hidden values

Notice the values used for the function_num argument are "paired" (e.g. 1-101, 2-102, 3-103, ...). This is related to how SUBTOTAL deals with manually hidden rows. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden. When function_num is between 101-111, SUBTOTAL ignores values in rows that have been manually are hidden.

SUBTOTAL always ignores values in cells are hidden with a filter. Values in "filtered out" rows are not included, regardless of function_num.

Notes

When function_num is between 1-11, SUBTOTAL includes values that are hidden
When function_num is between 101-111, SUBTOTAL excludes values that are hidden
In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
SUBTOTAL is designed to work with vertical data values arranged vertically. In Horizontal ranges, values in hidden columns are always included.

Good links

Microsoft SUBTOTAL function documentation

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.