Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel Tip: Explore SUM's nuances

by
15th Oct 2014
Save content
Have you found this content useful? Use the button above to save it to your profile.

David H Ringstrom explores some of the quirks of the most popular Excel function: SUM. 

Most Excel users use the SUM function to sum a single range, such as =SUM(B2:B8).

Users can actually add up to 255 different ranges together, although such a formula would be tough to write, and tougher to audit. If users wish to add two or more ranges, simply separate each range with a comma, as shown here:

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

But 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 instantly get a formula that sums rows 2 through 8 in each column.

Then let's say that I decide to insert a new row above the first line of what's presently being summed. As shown below, 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 totalled either. The solution to both of these problems involves using "buffer" rows, where we sum one row above and one row below the values that we're tallying.

Pressing Alt-= makes it easy to sum adjacent rows or columns

But SUM has its shortcomings. For example, the ubiquitous function doesn't automatically pick up new rows that you add, as shown here:

The SUM function doesn't automatically pick up new rows that you add

So the workbook needs a few tweaks. Choosing to use buffer rows doesn't mean you have to forgo using Alt-=, but you do need to adjust the selection range.

If you place your cursor of cell B11 in the table shown below and then press Alt-=, Excel will create a SUM function that includes the bottom buffer row. To tweak the formula, drag the blue handle up one row. You won't be able to make this adjustment if you select cells B10:E10; Excel will fill the cells with the formula.

Minimise data integrity risk by incorporating buffer rows into your spreadsheets

Your can also extend the sum range by moving the blue selection handle up one row after you press Alt-=, as seen here:

Using buffer rows is my preferred approach for improving the integrity of the SUM function, but you can also rely on Excel's Error Checking feature.

As shown in the next image, a green tick mark appears in cell B10, which you can use to correct your formula:

Use Excel's error checking menu 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.

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 below
    • A user clears the checkbox for Formulas which omit cells in a region, as seen in the next image
    • A user chooses Ignore Error on the error checking menu in shown in the image above, instead of expanding the range.

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 below. If a sum amount does not appear on the status bar, right-click on the status bar and choose SUM.

The Status Bar provides a simple means of verifying SUM functions

Replies (2)

Please login or register to join the discussion.

avatar
By maxmillion
25th Oct 2014 13:24

Fabulous!

Thank you for the tips. I especially love the status bar right click option. Never knew that was there. And I like the Alt = option. A helpful, bite-sized lesson that is not overwhelming.

Thank you again.

-M

Thanks (0)
avatar
By edhy
04th Dec 2014 09:39

Sum Intersection

We can sum intersection (set theory) of two ranges by placing space between two ranges, for union comma is placed.

e.g. =SUM(B1:B8 A5:C6) will result B5+B6.

Though I have not found any use of this in accounting or finance.

Zubair Edhy

Thanks (0)