Excel SUBTOTAL function with formula examples

1. Calculate values in filtered rows

Because the Excel SUBTOTAL function ignores values in filtered-out rows, you can use it to create a dynamic data summary where subtotal values are re-calculated automatically according to the filter.

For example, if we filter the table to show sales only for the East region, the Subtotal formula will automatically adjust so that all other regions are removed from the total:

2. Calculate only visible cells

As you remember, Subtotal formulas with function_num 101 to 111 ignore all hidden cells - filtered out and hidden manually. So, when you use Excel's Hide feature to remove irrelevant data from view, use function number 101-111 to exclude values in hidden rows from subtotals.

The following example will help you gain more understanding of how it works: Subtotal 9 vs. Subtotal 109.

3. Ignore values in nested Subtotal formulas

If the range supplied to your Excel Subtotal formula contains any other Subtotal formulas, those nested subtotals will be ignored, so the same numbers won't be calculated twice. Awesome, isn't it?

In the screenshot below, the Grand Average formula SUBTOTAL(1, C2:C10) ignores the results of the Subtotal formulas in cells C3 and C10, as if you used an Average formula with 2 separate ranges AVERAGE(C2:C5, C7:C9).

Example 1. Subtotal 9 vs. Subtotal 109

As you already know, Excel SUBTOTAL accepts 2 sets of functions numbers: 1-11 and 101-111. Both sets ignore filtered-out rows, but numbers 1-11 include manually hidden rows whereas 101-111 exclude them. To better understand the difference, let's consider the following example.

To total filtered rows, you can use either Subtotal 9 or Subtotal 109 formula like shown in the screenshot below:

But if have hidden irrelevant items manually by using the Hide Rows command on the Home tab > Cells group > Format > Hide & Unhide, or by right clicking the rows, and then clicking Hide, and now you want to total values only in visible rows, Subtotal 109 is the only option:

Other function numbers work in the same way. For example, to count non-blank filtered cells, either Subtotal 3 or Subtotal 103 formula will do. But only Subtotal 103 can properly count visible non-blanks if there are any hidden rows in the range:

Example 2. IF + SUBTOTAL to dynamically summarize data

If you are creating a summary report or a dashboard where you have to display various data summary but you don't have space for everything, the following approach might be a solution:

• In one cell, make a drop-down list containing the functions names such as Total, Max, Min, and so on.
• In a cell next to the dropdown, enter a nested IF formula with the embedded Subtotal functions corresponding to the function names in the drop-down list.

For example, assuming the values to subtotal are in cells C2:C16, and the drop-down list in A17 contains Total, Average, Max, and Min items, the "dynamic" Subtotal formula is as follows:

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

And now, depending on what function your user selects from the drop-down list, the corresponding Subtotal function will calculate values in filtered rows: