# SUBTOTAL

Returns a subtotal for a vertical range of cells using a specified aggregation function.

### Sample Usage

SUBTOTAL(9,C2:C5)
SUBTOTAL(1,C2:C5,D2:D8)

### Syntax

SUBTOTAL(function_code, range1, [range2])

• function_code - The number that specifies the function to use for the subtotal.
• 1 for AVERAGE

• 2 for COUNT

• 3 for COUNTA

• 4 for MAX

• 5 for MIN

• 6 for PRODUCT

• 7 for STDEV

• 8 for STDEVP

• 9 for SUM

• 10 for VAR

• 11 for VARP

• range1 - The first range over which to calculate the subtotal.
• range2 - Additional ranges over which to calculate subtotal(s).

### Examples

SUBTOTAL(9,C2:C5) returns the sum of the subtotal of the cells C2:C5.
SUBTOTAL(1,C2:C5,D2:D8) returns the average of the numbers contained in the ranges C2:C5 and D2:D8.

### Notes

• If you want to skip the hidden values in your calculation, use the following codes:
• 101 for AVERAGE
• 102 for COUNT
• 103 for COUNTA
• 104 for MAX
• 105 for MIN
• 106 for PRODUCT
• 107 for STDEV
• 108 for STDEVP
• 109 for SUM
• 110 for VAR
• 111 for VARP
• The SUBTOTAL function ignores any rows that are not included in the result of a filter, irrespective of the function_code used.
• Using SUBTOTAL helps prevent double-counting associated with the SUM function. If there are other subtotals within range1, range2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
• The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_code of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.