SUM vs. SUBTOTAL Functions

SUBTOTAL

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.

The usual application of the SUM function.
The usual application of the SUM function
Finding the total using SUBTOTAL function
Finding the total using SUBTOTAL function

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.

The SUM function double-counts when subtotals are within the range
The SUBTOTAL function does not double-count
The SUBTOTAL function does not double-count

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).

Performing an average within SUBTOTAL
Performing an average within SUBTOTAL

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.

Two functions adding values with no filter applied getting the same result
Adding Values with No Filter Applied
Two functions adding values with a filter applied getting different results
Adding Values with a Filter 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.

Data in the table has been moved around
New Arrangement of Data

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.

Indenting Rows

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.

Results of SUM combined with CHILDCELLS

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.

Leave a comment

© 2020 – learnspreadsheet.com