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. Despite the name, SUBTOTAL has the ability to perform a variety of math functions, including AVERAGE, COUNT, MAX, and many others (see table below for the complete list). By default, SUBTOTAL excludes values in rows hidden by a filter, which makes SUBTOTAL very useful in Excel Tables.

The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.

Examples

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 calculations

SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. There are 11 functions available, each with two options, as seen in the table below. Notice the values are "paired" (e.g. 1-101, 2-102, 3-103, and so on). 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 excludes values in rows that have been manually hidden.

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

Note: SUBTOTAL always ignores values in cells that are hidden with a filter. Values in rows that have been "filtered out" are never included, regardless of function_num.

SUBTOTAL in Excel Tables

The SUBTOTAL function is used when you display a Total row in an Excel Table. Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc. The reason Excel uses SUBTOTAL for calculations in the Total row of an Excel Table is because SUBTOTAL automatically excludes rows hidden by the filter controls at the top of the table. That is, as you filter rows in a table with a Total row, you'll see the calculations update automatically to respect the filter.

SUBTOTAL with outlines

Excel has a Subtotal feature that automatically inserts SUBTOTAL formulas in sorted data. You can find this feature at Data > Outline > Subtotal. SUBTOTAL formulas inserted this way use the standard function numbers 1-11. This allows the subtotal results to remain visible even as rows are hidden and displayed when the outline is collapsed and expanded.

Note: although the Outline feature is an "easy" way to insert subtotals in a set of data, a Pivot Table is a better and more flexible way to analyze data. In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice.

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 SUBTOTAL formulas that exist in references 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.