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

Use the SUBTOTAL function to get a subtotal in a list or database. Despite the name, SUBTOTAL has the ability to perform a variety of math functions, including AVERAGE, COUNT, MAX, and many others (see table below for the complete list). By default, SUBTOTAL *excludes* values in rows hidden by a filter, which makes SUBTOTAL very useful in Excel Tables.

The SUBTOTAL function automatically ignores other SUBTOTAL formulas that exist in references to prevent double-counting.

### Examples

Below are a few 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:

### 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 the 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. The reason Excel uses SUBTOTAL for calculations in the Total row of an Excel Table is 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, you'll see the calculations update automatically to 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 is designed to work with vertical data values arranged vertically. In horizontal ranges, values in hidden columns are always included.