Excel SUBTOTAL Function

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

FunctionIncludes Manually Hidden rowsExcludes Manually Hidden rows
AVERAGE1101
COUNT2102
COUNTA3103
MAX4104
MIN5105
PRODUCT6106
STDEV.S7107
STDEV.P8108
SUM9109
VAR.S10110
VAR.P11111

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.

Subtotal Function 1

Rows Hidden by a Fliter

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

Subtotal Function 2

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.

Subtotal Function 3

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

Subtotal Function 4

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.

Subtotal Function 5
Subtotal Function 6

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

Subtotal Function 7

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

Subtotal Function 8

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

Subtotal Function 9

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

Subtotal Function 10

Result:

Subtotal Function 11

Leave a Comment