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

Sample Usage



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


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.


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