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?
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
- 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
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)
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:
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
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.