Errors that can happen in simple SUM formulas

When reviewing a financial model, it is important to understand the typical errors people make. Below are ideas on easy ways of finding the SUM errors, in order of riskiness, when auditing a financial model.

It is useful to have an spreadsheet error add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.

SUM ignores cells that it does not see as a number

The SUM function acts similar to AVERAGE, MAX and MIN in that it

  • ignores blanks (not an issue here as blank and zero would give the same result),
  • ignores anything that doesn’t look like a number which could be word, or it could be a number formatted as text or a number that has a strange space in.

Below a listing of the SUM functions from the Formula Report.

Finding the SUM errors

If you go and look at the cell, C27 has the individual calculation (=C9+C10+..) and it is giving a VALUE error. The SUM in C25 is giving a number but it is horribly wrong as just the first cell in the range is bigger than it. The SUM function is ignoring any number bigger than 3 characters due to a unique space type that Excel sometimes sees. So when you see a SUM, remember to be concerned if you find any numbers that are coming into Excel in a strange format or else treated by Excel as text.

Finding the SUM errors

References to a long list of individual cells

The other risk is when there is a long list of cells that, presumably, a user has clicked on individually either within a SUM or with +’s. The risk here is simply pointing to the wrong cell.

So, below, although this looks like a simple formula, it is generally quick to check and it will either immediately look right or wrong.

Finding the SUM errors

In this case you will see that the cell D9 has been missed as the person clicked on D8 instead.

Finding the SUM errors

Summing through sheets or 3D formula

There is a way to use a SUM formula to sum through sheets, sometimes referred to as 3D formula. Effectively it means that Excel will add the same cell from every sheet BETWEEN a start and end sheet. The risk here is that a user does not understand how the formula works and moves the sheets around for some other reason (easier to compare 2 sheets if next to each other) but this changes the results.

As shown below, if you have a sum which seems to only refer to a single cell (G7 below) but has 2 sheet names, it is a 3D formula. In this case the order of the sheets becomes important so either they need to be protected or all users must be aware of what happens when you move sheets around.

Sum Error in Excel

Sum cells by colour with the Data Filter

Counting or summing coloured cells in Excel- formula