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], ...)
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 function_num argument: =SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE
In the worksheet shown above, the formulas in C4 and F4 are: =SUBTOTAL(3,B7:B19) // count visible =SUBTOTAL(9,F7:F19) // sum visible
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 works with vertical data. In horizontal ranges, values in hidden columns are always included.
There are several other articles for different topics, please visit other categories or use the search button to find more.
