SUBTOTAL is a little-known function in spreadsheet programs. Despite its robustness, it is far less prevalent than functions such as SUM and AVERAGE. Let’s look at what you can do with this function in spreadsheet.com using this template to follow along.
Contents
Purpose
SUBTOTAL returns an aggregate result for a range of cells based on the specified function code.
Video Explanation
Syntax
=SUBTOTAL(function_code, range1, [range2,...])
function_code– The function to use for the aggregation.range1– The first range for which to calculate the selected function.range2…– [OPTIONAL] Additional ranges over which to calculate the chosen function.
Examples
There are many situations where the SUM function falls short, in which case the SUBTOTAL with a function code of 9 acts as a substitute. Let’s look at an example.
Example 1 – Using SUBTOTAL to SUM a Range
If you had a dataset containing subtotals for different categories and you wanted to find the total, you would have a few options at your disposal.
For example, you could use the + operator to add the cells one by one =cell1+cell2+cell3+... which could be tedious. Similarly, you could use the SUM function, which is susceptible to double counting. Instead, we could use SUBTOTAL to make the job much easier.
Before we look at how we can use SUBTOTAL in place of SUM, let’s understand how it works. As aforementioned, the syntax is =SUBTOTAL(function_code, range1, [range2],...). The function code is a predefined constant that represents what operation you wish to perform. You can apply any of the 11 operations within the function that are listed above.
If we wanted to get the total of a range containing subtotals, our number of interest would be 9 because 9 corresponds to SUM. As a result, the formula would be =SUBTOTAL(9, range1, [range2],...). There is one caveat, however. You have to calculate the smaller totals first using the SUBTOTAL function. Lost? Take a look at the following dataset:
We can quickly see that the above total is incorrect. SUBTOTAL is adding all values in the specified range regardless of whether they are a total or not. This double-counting isn’t helpful to us and is quite similar to what the SUM function would do. How can we fix this?
We have to calculate the totals in cells B7 and B15 using the SUBTOTAL function with a function code of 9 so that the total will ignore these cells. After making this switch, this is what we get:
Nice! The formula now gives us the correct total.
Example 2 – SUBTOTAL with Filtered Data
If you are using filters in your spreadsheet, the SUBTOTAL will treat the filtered data differently than the underlying function, such as AVERAGE. The AVERAGE function will include filtered rows in its calculation.
Note that AVERAGE is one of the eleven functions available, and this difference is true for all eleven.
The SUBTOTAL function will exclude filtered data by only using the visible rows after filters are applied.
Example 3 – The Cell Indentation Alternative
With the ability to create row hierarchies in spreadsheet.com, you can derive subtotals differently. Depending on how you structure your data, using functions specifically made for row hierarchies could provide a more robust method of subtotaling groups of data.
The 🔒template for this example is available to members of learnspreadsheet.com.
First, we have rearranged the data so that you can set up row hierarchies. The “North Region” and “South Region” have been moved above their respective salespeople, and the label of “Salesperson” is at the top. The empty cells in column B are ready for our new formulas.
After arranging the data, select each block of data to be grouped. Choose the Indent rows option on the menu, as shown in the animation below.
The CHILDCELLS formula does not need a cell range as long as you place it in the empty parent cell. =CHILDCELLS() with nothing inside the parenthesis will work, which we used in this example.
Now that we combined the SUM formula and the CHILDCELLS formula into =SUM(CHILDCELLS()), you can get subtotals without worrying about double counting.
See Also
Read more detail on the indentation feature to nest data under parent cells. Indentation allows subtotaling using the CHILDCELLS function.
Learn how to choose between the SUM function and the SUBTOTAL function.
