Sum Error in Excel

During a training course we discovered an interesting inconsistency causing a sum error in Excel.

As per the image we have 2 columns of data and we have summed them using a sum function or adding up the individual cells.


Note that in row 4 we have purposefully inserted a number with an apostrophe in front making it a text. In this case the SUM function ignores it (treats it as zero), whereas the individual cells calculation seems to adjust for it and includes it.

For longer lists it is more likely that the SUM function would be used and in this case it causes an error. It is important to watch out for numbers that Excel considers as text. Watch out for numbers that are left aligned or have the green error marks in the top left of a cell.

Learn more at our advanced excel classes.

Errors that can happen in simple SUM formulas