ID EN
…
#Math

SUBTOTAL

Excel Functions

Get a subtotal in a list or database

Syntax

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

Arguments

Parameter Description
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.

Return Value

A number representing a specific kind of subtotal

Details

The SUBTOTAL function is designed to run a given calculation on a range of cells while ignoring cells that should not be included. SUBTOTAL has three features that make it especially useful: Because SUBTOTAL ignores cells that have been "filtered out", it is especially useful in Excel Tables or filtered data. In addition, SUBTOTAL can be optionally set to exclude values in rows that have been manually hidden (i.e. rows hidden with a shortcut or by Right click > Hide). Regardless of the calculation performed, SUBTOTAL returns single aggregate result from a set of data. Finally, while SUBTOTAL is good at ignoring things, it does not ignore errors. If you need capability, see the AGGREGATE function. Note: the SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in refere

Examples

Examples

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

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

In the worksheet shown above, the formulas in C4 and F4 are:

EXCEL 
=SUBTOTAL(3,B7:B19) // count visible
=SUBTOTAL(9,F7:F19) // sum visible

See Also

AGGREGATE