The subtotal function in Excel is very handy when you constantly apply filters to a dataset and need to see results instantly. Regular SUM, COUNT, AVERAGE, MAX, etc., would continue showing results for the whole data regardless of what you filter, whereas the SUBTOTAL Function would ignore values of rows hidden by a filter or rows manually hidden. Today, we will take you through the workings of the SUBTOTAL Function.

## Syntax & Argument

The Syntax of the SUBTOTAL Function is:

=SUBTOTAL(function_num, ref1, [ref2], …)

The arguments for this function are the function_num, the ref1, ref2 and so on. Let’s see what these arguments mean:

- function_num: This refers to the number that specifies the function to calculate subtotals in the range you specify in the next argument. There is no need to cram numbers, however, because immediately you type “=SUBTOTAL(” in Excel, you will see a dropdown containing numbers of various functions. For example, 9 is for Average, while 4 is for MAX.
- ref1: This is the range to perform the subtotal function you specify above
- ref2 (optional): You can specify a second range to perform the subtotal function on.

### Things to note

Some things to note when using the SUBTOTAL function are:

- The SUBTOTAL Function returns an aggregate calculation for specified ranges. This means that if you specify ref1, ref2 ref3 etc, the SUBTOTAL Function would return an aggregate calculation (e.g SUM, MAX, MIN) for any of the rows that are visible.
- By Default, SUBTOTAL Function excludes values in rows hidden by a filter, but they also exclude values in rows hidden manually.
- SUBTOTAL Function ignores other SUBTOTAL Functions that may be contained in the data set to prevent a possible double counting.

Now, let’s move on to available calculations on the SUBTOTAL Function and their numbers

## Available Calculations

Function | Includes Manually Hidden rows | Excludes Manually Hidden rows |
---|---|---|

AVERAGE | 1 | 101 |

COUNT | 2 | 102 |

COUNTA | 3 | 103 |

MAX | 4 | 104 |

MIN | 5 | 105 |

PRODUCT | 6 | 106 |

STDEV.S | 7 | 107 |

STDEV.P | 8 | 108 |

SUM | 9 | 109 |

VAR.S | 10 | 110 |

VAR.P | 11 | 111 |

## SUBTOTAL Function Examples

In this section, we have the budget of a clinic that is looking to mass produce COVID19 Vaccines. The Spend requests have been categorized according to relevance. **Category** **A** are expenses that are most critical to the production process whilst **Category** **C** are least critical to the production process.

### Rows Hidden by a Fliter

Now let’s add up all cost with a regular SUM function without applying any filter:

Let’s assume that we are only interested in Category A and Category B for our review with the Medical Director, we would need to filter the data to show only information relevant to the said director. After filtering, we notice that total cost we calculate above is unchanged even though we have hidden 2 entries through the filter button.

To show the total cost for Categories A & B only, we use the SUBTOTAL Function.

### Rows Manually Hidden

Notice that we used 109 for our SUM instead of 9. This is just in case we need to manually hide one entry in a category during the presentation. For example, we may be asked to hide the row for Other Clinical Costs manually. Using “9” would still include the value of other Clinical costs in our calculation whilst using 109 would Ignore Other Clinical Costs and show the true picture.

## Automatic Subtotals

For the automatic subtotals, we need to first convert our dataset into tables. To do this, highlight your data and navigate to Insert > Table

Check that the create a table box is referencing the correct cells in the “where is the data for your table?” input box

**Next step** is to navigate to Table Design tab and click on Total Row

Data selected would be turned into a table, now go the total rows row under cost and in the drop down, select SUM

**Result:**