SUBTOTAL

Get a subtotal in a list or database

Sponsored Link

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

Arguments 

  • function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
  • ref1 - A named range or reference to subtotal.
  • ref2 - [optional] A named range or reference to subtotal.

Usage notes 

Use the SUBTOTAL function to get a subtotal in a list or database. SUBTOTAL has the ability to use a variety of functions when subtotaling, including AVERAGE, COUNT, MAX, and others (see table below for a complete list). In addition, the SUBTOTAL function can either include or exclude values in rows that are not visible.

Notice that the SUBTOTAL function has "paired" settings (i.e. 1/101, 3/103, 9/109, etc.) to change behavior for hidden cells. When function_num is between 1-11, SUBTOTAL includes cells that are hidden. When function_num is between 101-111, SUBTOTAL excludes values that are hidden.

SUBTOTAL always ignores all cells that are hidden by filtering with Autofilter, so all cells that are "filtered out" will not be included in calculations, regardless of the function_num that is used.

To create a list with subtotals, you might want to use the Subtotal command in the Outline group on the Data tab in the ribbon. You can then modify the SUBTOTAL function if needed.

Available values for function_num:

FunctionInclude hiddenIgnore hidden
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV7107
STDEVP8108
SUM9109
VAR10110
VARP11111

Notes:

  • 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 subtotals that exist in references are ignored to prevent double-counting
  • SUBTOTAL is designed to work with vertical data values arranged vertically. In Horizontal ranges, values in hidden columns are always included.

Sponsored Link

0 votes. 0 / 5