Unlike the SUBTOTAL function, the SUM function is one of the most popular functions in spreadsheet workflow. This popularity is because we often add values in our dataset. However, it falls short in some situations because there is a more efficient way to do things. That’s where SUBTOTAL comes in. In this article, we will look at different situations that warrant the use of either of the two functions in spreadsheet.com.
Use this template to follow along with the first three examples.
Contents
Example 1 – Adding Values that Contain Subtotals
In a case where the data does not contain subtotals, both functions work well. For instance, we can get the total sales using both SUM and SUBTOTAL in the following dataset.
If subtotals created by the SUM function exist within the range to sum, we must be careful since adding the entire range with the SUM function results in double-counting. In such a case, SUBTOTAL comes out as the winner. You can find a more in-depth discussion about the syntax and working of the SUBTOTAL function in this article.
Example 2 – Carrying Out other Operations besides SUM
Another difference between the two functions is that while SUM is limited to addition, you can perform other functions within the SUBTOTAL function. For example, we can perform an average instead of the total by specifying the correct function code. Note the 1 at the beginning of the function =SUBTOTAL(1,B3:B15).
Example 3 – SUM and SUBTOTAL with Filtered Data
If you are using filters in your spreadsheet, the SUM and the SUBTOTAL will treat the filtered data differently. The SUM function will include filtered rows in its calculation while the SUBTOTAL function will exclude filtered data by only using the rows that remain visible after filters are applied.
Example 4 – The Cell Indentation Alternative
With the ability to create row hierarchies in spreadsheet.com, you can derive subtotals without SUM or SUBTOTAL. 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 double counting.
Conclusion
Knowing which function to choose depends on what type of data you will be working with and how it is being used. The choice between SUM and SUBTOTAL can result in completely different results.