# Excel SUBTOTAL Function

The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.

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

The Excel SUBTOTAL function returns an aggregate calculation for supplied values. Despite the name, SUBTOTAL can perform a variety of calculations, including SUM, AVERAGE, COUNT, MAX, MIN, and others seen in the table below.

The SUBTOTAL function can either *include* or *exclude* values in hidden rows, which makes it especially useful in Excel Tables or filtered data. By default, SUBTOTAL *excludes* values in rows hidden by a filter. Regardless of the calculation performed, SUBTOTAL returns an aggregate result from a set of data.

*Note: the SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.*

### Examples

Below are examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range. Notice the only difference is the value used for the **function_num** argument:

In the worksheet shown above, the formulas in C4 and F4 are:

### Available calculations

SUBTOTAL behavior is controlled by the *function_num* argument, which is provided as a numeric value. There are 11 functions available, each with two options, as seen in the table below. Notice these values are "paired" (e.g. 1-101, 2-102, 3-103, and so on). This is related to how SUBTOTAL deals with manually hidden rows. When *function_num* is between 1-11, SUBTOTAL *includes* cells that have been *manually* hidden. When *function_num* is between 101-111, SUBTOTAL *excludes* values in rows that have been manually hidden.

Function |
Include hidden |
Ignore hidden |

AVERAGE | 1 | 101 |

COUNT | 2 | 102 |

COUNTA | 3 | 103 |

MAX | 4 | 104 |

MIN | 5 | 105 |

PRODUCT | 6 | 106 |

STDEV | 7 | 107 |

STDEVP | 8 | 108 |

SUM | 9 | 109 |

VAR | 10 | 110 |

VARP | 11 | 111 |

Note: SUBTOTAL *always* ignores values in cells that are hidden with a filter. Values in rows that have been "filtered out" are never included, regardless of *function_num*.

### SUBTOTAL in Excel Tables

The SUBTOTAL function is used when you display a Total row in an Excel Table. Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc. Excel uses SUBTOTAL for calculations in the Total row of an Excel Table because SUBTOTAL *automatically* excludes rows hidden by the filter controls at the top of the table. That is, as you filter rows in a table with a Total row, calculations automatically respect the filter.

### SUBTOTAL with outlines

Excel has a Subtotal feature that automatically inserts SUBTOTAL formulas in sorted data. You can find this feature at Data > Outline > Subtotal. SUBTOTAL formulas inserted this way use the standard function numbers 1-11. This allows the subtotal results to remain visible even as rows are hidden and displayed when the outline is collapsed and expanded.

*Note: although the Outline feature is an "easy" way to insert subtotals in a set of data, a Pivot Table is a better and more flexible way to analyze data. In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice.*

### 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 SUBTOTAL formulas that exist in references to prevent double-counting.
- SUBTOTAL works with vertical data. In horizontal ranges, values in hidden columns are
*always*included.

## Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.