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 david@acctadv.com 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.

 

You may like these other stories...

K2 Enterprises has announced its 2014 technology awards in 27 categories. The only clear message may have been that there was no clear message in a field marked by many good ideas, but no unanimous winners.The company, which...
We're all about QuickBooks this morning. First, read this late-breaking news from John Stokdyk, editor of AccountingWEB (U.K.), who is attending the QuickBooks Connect conference in San Jose, California. Then, for more...
Technology—specifically internet technology—has a record of disrupting tried-and-true methods of operation in ways that we often don't foresee. Look no further than the recent HBO announcement that they *gasp...

Already a member? log in here.

Upcoming CPE Webinars

Oct 30Many Excel users have a love-hate relationship with workbook links.
Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
Nov 12This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.
Nov 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.