# Excel SUBTOTAL function

## Summary

The Excel SUBTOTAL function returns an aggregate result for supplied values. This function can perform various operations like SUM, AVERAGE, COUNT, MAX, and more. It’s especially useful for calculating subtotals in a range that contains hidden rows or columns.

##### Syntax
```				```
=SUBTOTAL(function_num, ref1, [ref2], ...)
```
```
• function_num: A number that specifies the function to use for the subtotal (see the list below for a list of function numbers)
• ref1, [ref2], …: One or more cell ranges to calculate the subtotal for
##### Return value
A subtotal based on the specified function and range.

## How to use

SUBTOTAL is used to calculate subtotals in a list or database. It can perform different types of calculations, including sum, average, count, etc. This function is particularly useful in filtering data, as it only includes visible cells in calculations.

function_num values:

• `1`: AVERAGE
• `2`: COUNT
• `3`: COUNTA
• `4`: MAX
• `5`: MIN
• `6`: PRODUCT
• `7`: STDEV.S
• `8`: STDEV.P
• `9`: SUM
• `10`: VAR.S
• `11`: VAR.P
• `101`: AVERAGE (ignoring hidden rows)
• `102`: COUNT (ignoring hidden rows)
• `103`: COUNTA (ignoring hidden rows)
• `104`: MAX (ignoring hidden rows)
• `105`: MIN (ignoring hidden rows)
• `106`: PRODUCT (ignoring hidden rows)
• `107`: STDEV.S (ignoring hidden rows)
• `108`: STDEV.P (ignoring hidden rows)
• `109`: SUM (ignoring hidden rows)
• `110`: VAR.S (ignoring hidden rows)
• `111`: VAR.P (ignoring hidden rows)

## Examples

##### Simple SUBTOTAL
Subtotal for Summation: Calculating the sum of a range:
```				```
=SUBTOTAL(9, A2:A10)
```
```
This formula sums up the values in A2:A10.
##### SUBTOTAL for Averaging
Average of Visible Cells: Finding the average of a range, excluding hidden cells:
```				```
=SUBTOTAL(1, B2:B10)
```
```
This formula calculates the average of visible cells in B2:B10.
##### SUBTOTAL with Filters
Subtotal with Filtered Data: Summing filtered (visible) data in a range:
```				```
=SUBTOTAL(109, C2:C20)
```
```
In a filtered list, this formula sums only the visible (non-hidden) cells in C2:C20.
##### SUBTOTAL for Counting
Counting Visible Cells: Counting the number of visible cells with numeric data:
```				```
=SUBTOTAL(102, D2:D100)
```
```
Counts the number of visible numeric entries in D2:D100.
##### SUBTOTAL for Advanced Data Analysis
Dynamic Subtotals in Data Analysis: Creating dynamic subtotals that adjust with data filtering:
```				```
=Principal * (1 + InterestRate) ^ SQRT(Time)a=SUBTOTAL(101, E2:E200)
```
```
In this example, the average of E2:E200 is calculated, adjusting dynamically as rows are hidden or shown.