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

Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.