SUM vs. SUBTOTAL Functions

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.

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.