# 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. 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). By default, SUBTOTAL excludes values in rows hidden by a filter, as explained below. This makes SUBTOTAL very useful in Excel Tables.

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 functions

SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. The table below shows available functions:

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 |

### Manually hidden values

Notice the values used for the **function_num** argument are "paired" (e.g. 1-101, 2-102, 3-103, ...). 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 ignores values in rows that have been manually are hidden.

SUBTOTAL always ignores values in cells are hidden with a filter. Values in "filtered out" rows are not included, regardless of **function_num**.

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

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.