Excel SUBTOTAL function with formula examples
The tutorial explains the specificities of the SUBTOTAL function in Excel and shows how to use Subtotal formulas to summarize data in visible cells.
In the previous article, we discussed an automatic way to insert subtotals in Excel by using the Subtotal feature. Today, you will learn how to write Subtotal formulas on your own and what advantages this gives to you.
Excel Subtotal function - syntax and uses
Microsoft defines Excel SUBTOTAL as the function that returns a subtotal in a list or database. In this context, "subtotal" is not just totaling numbers in a defined range of cells. Unlike other Excel functions that are designed to do only one specific thing, SUBTOTAL is amazingly versatile - it can perform different arithmetic and logical operations such as counting cells, calculating average, finding the minimum or maximum value, and more.
The SUBTOTAL function is available in all versions of Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.
The syntax of the Excel SUBTOTAL function is as follows:
Where:
- Function_num - a number that specifies which function to use for the subtotal.
- Ref1, Ref2, … - one or more cells or ranges to subtotal. The first ref argument is required, others (up to 254) are optional.
The function_num argument can belong to one of the following sets:
- 1 - 11 ignore filtered-out cells, but include manually hidden rows.
- 101 - 111 ignore all hidden cells - filtered out and hidden manually.
| Function_num | Function | Description | |
| 1 | 101 | AVERAGE | Returns the average of numbers. |
| 2 | 102 | COUNT | Counts cells that contain numeric values. |
| 3 | 103 | COUNTA | Counts non-empty cells. |
| 4 | 104 | MAX | Returns the largest value. |
| 5 | 105 | MIN | Returns the smallest value. |
| 6 | 106 | PRODUCT | Calculates the product of cells. |
| 7 | 107 | STDEV | Returns the standard deviation of a population based on a sample of numbers. |
| 8 | 108 | STDEVP | Returns the standard deviation based on an entire population of numbers. |
| 9 | 109 | SUM | Adds up the numbers. |
| 10 | 110 | VAR | Estimates the variance of a population based on a sample of numbers. |
| 11 | 111 | VARP | Estimates the variance of a population based on an entire population of numbers. |
In fact, there is no need to memorize all function numbers. As soon as you start typing a Subtotal formula in a cell or in the formula bar, Microsoft Excel will display a list of available function numbers for you.
For example, this is how you can make a Subtotal 9 formula to sum up the values in cells C2 to C8:
To add a function number to the formula, double-click on it, then type a comma, specify a range, type the closing parenthesis, and press Enter. The completed formula will look like this:
=SUBTOTAL(9,C2:C8)
In a similar manner, you can write a Subtotal 1 formula to get an average, Subtotal 2 to count cells with numbers, Subtotal 3 to count non-blanks, and so on. The following screenshot shows a few other formulas in action:
Now that you know how to create a Subtotal formula in Excel, the main question is - why would one want to take the trouble of learning it? Why not simply use a regular function like SUM, COUNT, MAX, etc.? You will find the answer right below.
Top 3 reasons to use SUBTOTAL in Excel
Compared to the traditional Excel functions, SUBTOTAL gives you the following important advantages.
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).
Using Subtotal in Excel - formula examples
When you first encounter SUBTOTAL, it may seem complex, tricky, and even pointless. But once you get down to brass tacks, you will realize that it's not that difficult to master. The following examples will show you a couple of helpful tips and inspirational ideas.
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:
SUBTOTAL(109, A1:E1) to sum numbers in a horizontal range, hiding a column won't affect the subtotal.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:
Excel Subtotal not working - common errors
If your Subtotal formula returns an error, it's likely to be because of one of the following reasons:
#VALUE! - the function_num argument is other than an integer between 1 - 11 or 101 - 111; or any of the ref arguments contains a 3-D reference.
#DIV/0! - occurs if a specified summary function has to perform a division by zero (e.g. calculating an average or standard deviation for a range of cells that does not contain a single numeric value).
#NAME? - the name of the Subtotal function is misspelled - the easier error to fix :)
Finally, you are welcome to download the Excel Subtotal sample workbook containing all of the formulas we discussed in this tutorial.
