Syntax. SUBTOTAL(function_num,ref1,ref2, ...)
Definition. This function returns a subtotal in a list or database. It is usually easier to create a list with subtotals by using the Data/Subtotal command. After the subtotal list has been created, you can modify it with the SUBTOTAL() function.
Arguments
function_num (required) A number between 1 and 11 (including hidden values) or between 101 and 111 (ignoring hidden values) that specifies which function to use when calculating subtotals within a list. The possible values are shown in Table 16-6.
ref1 (required) and ref2 (optional) The range or reference for which you want to calculate the subtotal
Background. Though simple formulas include filtered values, the SUBTOTAL() function can refer to filtered results. If there are other subtotals within the ref arguments (nested subtotals), these nested subtotals are ignored to avoid double counting.
If any of the references are 3-D references, the SUBTOTAL() function returns the #VALUE! error.
For the function_num constants 1 through 11, the SUBTOTAL() function includes the values of rows hidden by the Format/Row/Hide command (Excel 2003) or the Start/Cells/Hide & Unhide command (Excel 2007 or Excel 2010). Use these constants to subtotal hidden and nonhidden numbers in a list.
For the function_num constants 101 through 111, the SUBTOTAL() function ignores the values of rows hidden by the Format/Row/Hide command (Excel 2003) or the Start/Cells/Hide & Unhide command (Excel 2007 or Excel 2010). Use these constants to subtotal only nonhidden numbers in a list.
The SUBTOTAL() function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
The SUBTOTAL() function is designed for columns of data or vertical ranges, not for rows of data or horizontal ranges. For example, when you subtotal a horizontal range using a function constant of 101 or greater, as in =SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. However, hiding a row in a subtotal of a vertical range does affect the subtotal.
Example. You can use the SUBTOTAL() function to calculate a sum based on filtered values (see Figure 16-30).
The formula is: =SUBTOTAL(9,C2:C8).
To view the sales for Michigan, specify a filter in column A (see Figure 16-31).
