SUBTOTAL()

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.

  Table 16-6. Possible Values for the function_num Argument

  Code for Unhidden Values	Code for Hidden Values	Function
  1	101	AVERAGE()
  2	102	COUNT()
  3	103	COUNTA()
  4	104	MAX()
  5	105	MIN()
  6	106	PRODUCT()
  7	107	STDEV()
  8	108	STDEVP()
  9	109	SUM()
  10	110	VAR()
  11	111	VARP()

• 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).

Figure 16-30. The sum of the sales in E1 is calculated for the filtered values.

The formula is: =SUBTOTAL(9,C2:C8).

To view the sales for Michigan, specify a filter in column A (see Figure 16-31).

Figure 16-31. Subtotal of the sales.

See Also

AGGREGATE(), AVERAGE(), COUNT(), COUNTA(), MAX(), MIN(), PRODUCT(), STDEV(), SUM(), VAR()

Sample Files

Use the SUBTOTAL and SUBTOTAL2 worksheets in the Chapter16.xls or Chapter16.xlsx sample file. The sample files are found in the Chapter16 folder. For more information about the sample files, see the section titled Using the Sample Files.