Problems to Be Aware of When You Use the Excel SUM Function

Excel SUM is one of the most used functions but you may encounter some problems during normal use.

To illustrate a typical problem with Excel SUM formulas, see graphic 1 below. The formulas for B4 and D4 are identical: =SUM(B1:B3) and =SUM(D1:D3) respectively. Did you notice the different result?

Graphic 1

You will see why this does happen and how to solve it, and more below…

1) Lower Total-Result Due to Numbers Stored as Texts

SUM formula doesn’t auto convert numbers-stored-as-text into numbers. These pseudo-numbers may be undetected and non-considered in your Total result.

The causes may be:

  • Importing Data from other sources; for example: csv files with leading and trailing spaces
  • Numbers preceded by an apostrophe (’) which convert numbers into texts
  • Wrong decimal delimiter in source data: (,) instead of (;) or vice versa

The solutions may be:

  • Create a check column with ISNUMBER function to test any undetected problem
  • Convert a text that represents a number into a number with VALUE function
  • Edit the cell individually and press ENTER
  • Set the correct decimal delimiter (,) or (;) by changing Regional Settings in Windows Control Panel before importing
  • Select cells/range, click the error button that appears to the left, and then click Convert to Number

Important notice

  • When a number is stored as texts this message appears: “Numbers formatted as text or preceded by an apostrophe”
  • Be sure that Background error checking is enabled. Proceed this way:
    • For Excel 2007 users, go to Office Button>Excel options>Formulas>Error checking>Enable background error checking.
    • For Excel 2003 users, go to Tools>Options>Error Checking> Enable background error checking.
  • SUM formula does not detect numbers-stored-as-texts but these pseudo numbers are converted to numbers when you use a formula with the operator (+). For example: Let’s assume that A2 is a number stored as text. The formula =SUM(A1:A3) is wrong while the formula =A1+A2+A3 is correct; try it out
  • Numbers-stored-as-texts are automatically formatted to the left
  • Take care when numbers-stored-as-texts are aligned as numbers to the right and the Excel error message is turned off. Why? They are difficult to detect (D3 in graphic 1 is such text)

2) Lower Sum-Result Due to Non-Included Cells at the Bottom/Top/Left/Right of SUM Formula

Think this situation: you are done with your Total but sooner or later you insert a row/column at the top/bottom/left/right of the SUM formula.

For example (See graphic 2):

You insert row 15 and Excel SUM Formula does not automatically resize to include the added row. It remains =SUM(A12:A14).

The solution is:

  • Edit the formula directly and change it to =SUM(A12:A15) or
  • Press F2 and use the right-bottom-corner handle to resize the array

Graphic 2

Excel SUM, Cells not Included

 

3) Greater Sum-Result because Hidden Rows are Considered in Computation

Hidden rows are considered in SUM function. Such rows are the result of a filter or manual operation. Anyway, exclude them with SUBTOTAL function.

The function syntax is this one: SUBTOTAL(function_num, ref1, ref2, …)

Where “function_num” is:

  • 9 for SUM that includes hidden values
  • 109 for SUM that ignores hidden values

In brief, you would have a formula like this one: =SUBTOTAL(109,A1:A100)

Important notice
The function_num configuration does not apply to hidden columns.

4) SUM Formula Too Long

Have in mind that Excel 2003 supports only 1,024 characters in formula content; so avoid to be in the middle of a pick-arguments-marathon with a formula-too-long message.

Excel 2007 surpass above limitation, it is capable of manage up to 8,192 characters.

However the enhanced capabilities of new version, long formulas represent a thread for your work. Add intermediate steps instead of dealing with long formulas.

5) A SUM formula that retrieves an Error because at Least One Error/Problem Exist in Referenced Cells

Greater Sum-Result because more Cells are Considered in the SUM Formula
AutoSUM does not Calculate New Numbers Added In Range

The cause of this is that the source cells may contain errors:

  • Error of any kind in the referenced cells: #DIV/0!, N/A#, #REF! (because deleted cells)
  • Error in the formula because you delete some referenced cell. For example: =SUM(#REF!,G6,G11)
  • Numbers with currency symbols as part of the content ($10.00, €10.00, etc)
  • Numbers with percentage symbol as part of the content (23.3%, 10%, etc)

Suppress the error and the formula will be ok.

6) Greater Sum-Result because more Cells are Considered in the SUM Formula

This is not a problem of SUM function but a derived issue of Excel AutoSum capabilities.

AutoSum detects contiguous data region and pick the range automatically. It is a great tool but it can also play against you so be aware of range scope.

Using the keyboard instead of mouse keep you aware of data you include in your formulas.

Here’s an example:

Excel SUM, All cells included

7) AutoSUM does not Calculate New Numbers Added In Range

Sometimes you are frustrated because the quantities added does not affect Excel SUM results, don’t worry, the calculation may be set to manual. Press F9 and done.

To change this Excel configuration proceed this way…

For Excel 2007 users, go to: Office button>Excel options>Formulas>Workbook calculation>automatic

For Excel 2007 users, go to: Tools>Options>Calculation>Calculation>Automatic

Conclusion

Excel functions produce an output when you feed them with the correct arguments. These correct arguments are not necessarily the correct values.

I have learned that focusing on best practices is better than just feeding the formulas in the function wizard or formula bar.