Exploring the Nuances of Excel's SUM Function
My unscientific observation is that the SUM function is likely the most used—and abused—of all worksheet functions in Excel. Although the SUM function is far better than manually adding cells together (such as =B2+B3+B4+B5+B6+B7+B8), in this article I'll explore a couple nuances related to this venerable function.
Most Excel users use the SUM function to sum a single range, such as =SUM(B2:B8). You can actually add up to 255 different ranges together, although such a formula would be tough to write, and even tougher to audit. If you wish to add two or more ranges, simply separate each range with a comma, as shown in Figure 1.
Figure 1: Sum two or more ranges by separating each range with a comma.
If you're adding up a single range, then you should rarely need to actually type the SUM function. In any version of Excel, press Alt-= in a worksheet cell. Depending upon where your cursor is positioned, Excel should figure out your intent and automatically sum the adjacent rows or columns. Further, if you select two or more cells before you press Alt-= then Excel will fill each of the cells with a SUM.
However, such convenience sometimes invites data integrity risk into your spreadsheet. Let's say that I select cells B9:E9 in Figure 2 and press Alt-=. I do instantly get a formula that sums rows 2 through 8 in each column. However, let's say that I decide to insert a new row above the first line of what's presently being summed. As shown in Figure 3, this amount won't be included in my total.
The same issue arises if I insert a row just above the total, that row won't get totaled either. The solution to both of these problems involves using what I call "buffer" rows, where we actually sum one row above and one row below the values that we're tallying.
Figure 2: Pressing Alt-= makes it easy to sum adjacent rows or columns.
Figure 3: The SUM function doesn't automatically pick up new rows that you add.
Figure 4 shows the revised structure of our workbook. Using buffer rows doesn't mean we have to forgo using Alt-=. It just means we need to tweak the selection range. As shown in Figure 5, if I place my cursor in cell B11 and press Alt-=, Excel will create a SUM function that includes the bottom buffer row. To tweak the formula, I simply need to drag the blue handle up one row. Notice that you won't get the opportunity to make this adjustment if you select cells B10:E10; Excel will simply fill the cells with the formula.
Figure 4: Minimize data integrity risk by incorporating buffer rows into your spreadsheets.
Figure 5: Use your mouse to extend the sum range up one row after you press Alt-=.
Although using buffer rows is my preferred approach for improving the integrity of the SUM function, you can also rely on Excel's Error Checking feature. As shown in Figure 6, a green tick mark appears in cell B10, which you can use to correct your formula:
- Hover over the exclamation mark icon that appears, and then click the arrow to reveal the menu.
- Choose Update Formula to Include Cells.
Figure 6: Use Excel's error checking menu to correct your formula.
In my view there are three downsides to relying on the error checking feature to catch inconsistencies related to the SUM function:
- A repetitive action is required each time the error prompt comes up.
- The error checking prompt might not appear because:
- A user disables the Error Checking feature, as shown in Figure 7.
- A user clears the checkbox for Formulas which omit cells in a region, as shown in Figure 7.
- A user chooses Ignore Error on the error checking menu in shown in Figure 6, instead of expanding the range.
Figure 7: Ensure that your Error Checking options will notify you about omitted cells.
Fortunately, there's an easy means to verify most SUM functions: select the range of cells that is being summed, and confirm that the status bar shows the same total. In any version of Excel, if you select two or more cells then the total of said cells should appear in Excel's Status Bar, as shown in Figure 8. If a sum amount does not appear on the status bar, simply right-click on the status bar and then choose Sum.
Figure 8: The Status Bar provides a simple means of verifying SUM functions.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.