Regardless of the calculation performed, SUBTOTAL returns singleaggregate resultfrom a set of data.
Finally, while SUBTOTAL is good at ignoring things, itdoes notignore errors.
If you need capability, see theAGGREGATE function.
Note: the SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.
There are 11 calculations total, each with two options, as seen below.
Notice these values are “paired” (e.g.
1-101, 2-102, 3-103, and so on).
This is related to how SUBTOTAL deals withmanuallyhidden rows.
Whenfunction_numis between 1-11, SUBTOTALincludesrows that have beenmanuallyhidden.
Whenfunction_numis between 101-111, SUBTOTALexcludesrows that have been manually hidden.
Note: SUBTOTALalwaysignores values in cells that are hidden with a filter.
Values in rows that have been “filtered out” are never included, regardless offunction_num.
SUBTOTAL with outlines
Excel has a Subtotal feature that automatically inserts SUBTOTAL formulas in sorted data.
you’re free to find this feature at Data > Outline > Subtotal.
SUBTOTAL formulas inserted this way use the standard function numbers 1-11.
