It can be more difficult than it should be to determine when to use SUM or SUBTOTAL in Excel. We’ll explain what each one does, the different use cases and provide examples of each one in action. As a general rule, SUM is quick and straightforward while SUBTOTAL gives users additional options and can be a big time saver.
Excel SUM and SUBTOTAL functions are two of the most common Excel formulas in use today. While each one can be used to add up a range of values, they operate differently when there are hidden or filtered cells and when you are trying to setup an Excel sheet Grand Total vs a Subtotal.
Let’s jump in!
Table of Contents
How SUM Works in Excel
SUM is one of the most common functions in Excel, designed to add up a series of numbers. SUM allows you to add individual numbers, cell references, or ranges without having to manually add each individual cell together manually.
To use the SUM function, type =SUM followed by the numbers or cells you want to add enclosed in parentheses. For example, =SUM(E2:E135) would add all numbers from cells E2 through E13.
When you press Enter, Excel will accept the formula and return the resulting total. It’s a straightforward, time-saving tool to add a range, column or row of values together.
How Subtotal Works in Excel
The SUBTOTAL function in Excel is a bit more complex and versatile than SUM. It can be used to calculate the sum, average, count, max, min of a range of cells, with the option to skip hidden rows. It also skips preceding SUBTOTAL formulas allowing you to calculate the total of a full column that contains other subtotals.
The SUBTOTAL Syntax is different than a SUM, this is because the SUBTOTAL function is much more versatile. It allows you to go above and beyond adding a range of cells together, it allows you to perform various other calculations.
So the first step when using the SUBTOTAL formula is to define the type of calculation, in most cases it will be 9 for SUM. which is followed by a comma, and then the cell range is determined.
Here’s a screenshot of the different functions available.
And here is an example of a SUBTOTAL being used to add the same range as our previous example of E2 to E13
The following section explains what the different types of functions are, and what they do.
Types of Subtotal Functions in Excel
The Subtotal function in Excel’s first argument asks what type of function you want to perform. Usually it’s the number 9 to designate a SUM but there are other possible calculations and use cases for the Excel formula.
The following chart explains what each function does.
|Calculates the average of a range excluding hidden rows.
|Counts the number of cells containing numbers, ignoring hidden rows.
|Counts the number of non-empty cells, regardless of content, excluding hidden rows.
|Finds the maximum value in a range, excluding hidden rows.
|Finds the minimum value in a range, excluding hidden rows.
|Multiplies the cells in a range, excluding hidden rows.
|Estimates standard deviation based on a sample, excluding hidden rows.
|Calculates standard deviation based on the entire population, excluding hidden rows.
|Adds the cells in a range, excluding hidden rows.
|Estimates variance based on a sample, excluding hidden rows.
|Calculates variance based on the entire population, excluding hidden rows.
Defining How an Excel SUBTOTAL Handles Hidden Values
Each SUBTOTAL function has an alternate version that includes hidden rows in the calculations. To use these, you simply add 100 to the function number. For example, SUBTOTAL(109, range) would sum a range including hidden rows where SUBTOTAL(9, range) would exclude hidden rows.
The Difference Between SUM vs SUBTOTAL
There are two primary differences between SUM and SUBTOTAL in Excel. The first is that SUM will total hidden or filtered rows, while SUBTOTAL gives you the option to exclude them. The second is that SUBTOTAL excludes previous SUBTOTAL formulas enabling you to use one formula for section Subtotal and a Grand Total.
The Impact of Filtering Data
Filtering data in Excel allows you to display only the rows that meet certain criteria, making it easier to analyze your data. However, SUM and SUBTOTAL treat filtering differently which can have a big impact on your displayed results or your workflow.
SUM will always consider the entire data range regardless of whether a cell is visible, as long as it’s in the defined cell range. SUBTOTAL offer the flexibility to calculate based on only the visible (filtered) data.
Here’s an example using our dataset from earlier where we filtered out the bottom section of rows. The SUM column includes the values, which to the report viewer would think that it’s an incorrect total, where the SUBTOTAL column shows the values of only the filtered rows that are visible.
There are scenarios where you would want to use one over the other, or even both. For example, we’ll sometimes use both methods when working on bank reconciliations in Excel. a SUM column ensures that all values are being accounted for, but a SUBTOTAL lets you filter and try to figure out multiple transactions that net to a total on a bank statement.
Subtotal Excludes Previous Subtotals
For an example of using Subtotal to calculate Grand Totals, consider the following dataset. A grand total is being calculated in the bottom row, where each section has a subtotal of the 3 rows above it being calculated.
The column on the left only uses SUM formulas. While the initial subtotals for each section are calculating correctly, the SUM formula used for the grand total at the bottom adds up the values of the entire range of cells above. Of course we could write a formula that specific adds up each specific cell but we would have to update our formula if we added a section..
The column on the right side entirely uses SUBTOTAL formulas. The final SUBTOTAL formula at the bottom of the column returns the correct grand total because it excludes all of the SUBTOTAL formulas from the sections above when performing its calculation.
As you can see, the SUM and SUBTOTAL formulas interact differently, and SUBTOTALS can be a big time saver when working with lots of rows, sections, and groups of values that have been previously added together.
Excel Tip: If you ever want to see the formulas that make up an Excel workbook, press CTRL + ~ to switch from seeing the values in a workbook, to seeing all of the formulas in a workbook.
Using SUBTOTAL with Grouped Data
An scenario where the SUBTOTAL formula really shines is when combining it with grouped data in Excel. Grouping allows you to expand and collapse sections of your Excel spreadsheet. A group is designated by a Plus + sign on the rows or columns. When clicking the + symbol it expands the data. A SUBTOTAL allows you to easily calculate grand totals and subtotals for each section.
Here’s an example of grouped data, with grouping buttons on the left of the spreadsheet:
Here’s how to create groups in Excel:
- Select Rows to Group – First, select the rows or columns you want to group together.
- Group the Rows
- Go to the Data tab on the Ribbon.
- In the Outline group, click on the Group button.
- Choose Group Rows (or press ALT + SHIFT + RIGHT ARROW).
- Expand/Collapse Feature: Once grouped, you’ll see a small icon at the left of the grouped rows. Clicking this icon allows you to expand or collapse the group, making it easier to view or hide detailed data.
Troubleshooting When Excel SUM Duplicates
If Excel returns a SUM that’s exactly double of what is expected, double check the dataset to ensure that there are no hidden columns with subtotals in the cell range. Subtotals will be added to the underlying values in a range. To avoid this common error use a SUBTOTAL formula instead of a SUM formula to only add the underlying data.
Excel Keyboard shortcuts are a must for any Excel power user. They save time and give you street cred with other Excel users. For instance, ALT + = quickly inserts a SUM function. CTRL + SHIFT + L toggles filters on and off. Unfortunately, there is no keyboard shortcut for SUBTOTAL, but you can start typing the Subtotal formula and press TAB to auto-complete a formula so you don’t have to type the full word out as you’re writing your excel formula.
When to Use Sum vs Subtotal
Choosing between SUM and SUBTOTAL in Excel depends on your data and the context of your analysis. Use SUM when you need a straightforward total of a range, without concern for filters, hidden rows, or other subtotals. Try the keyboard shortcut to quickly add cell ranges together.
Choose SUBTOTAL when your data is subject to frequent filtering and you need calculations to adjust accordingly. Also use SUBTOTAL when working with a single column or row with other SUBTOTALS when you want to see a grand total. By using this function, you avoid having to manually update formulas that manually add individual cells together. An added benefit of this method is that you don’t have to offset columns of subtotals and grand totals providing an easier to read format for report viewers.
Luckily in Excel, you also don’t have to choose. You can mix and match or even use both formulas for different parts of your workbook which can be super useful for analysis.
Excel’s SUM and SUBTOTAL are some of the most widely used formulas in Excel. While SUM is best used for a straightforward total of cell range, and super-fast to deploy using keyboard shortcuts, SUBTOTAL provides an array of calculation options like summing, averaging, and counting while automatically adjusting to filtered data and ignoring other SUBTOTAL formulas that might be in the same cell range.
SUM is quick and easy while SUBTOTAL is more advanced and dynamic.