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.
Get a subtotal in a list or database
A number representing a specific kind of subtotal
=SUBTOTAL (function_num, ref1, [ref2], ...)
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.
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 behavior is controlled by the function_num argument, which is provided as a numeric value. The table below shows available functions:
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.
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.
If you want to count the number of visible items in a filtered list, you can use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter.
The SUBTOTAL function can perform calculations like...
If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL especially useful is that it...
To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET.
The SUBTOTAL function can easily generate sums and counts for hidden and non-hidden...
Because tables support structured references, you can learn a lot about a table with basic formulas. In this video, we run through about a dozen examples. It's a nice demo of how formulas and tables work together to save you time.
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.