Why a SUM formula is not working in a spreadsheet

See why a SUM formula returns a wrong total or no result in Excel or Google Sheets, and learn how to fix text numbers, error cells and manual calculation.

3 min read6 viewsWhy a SUM formula is not working in a spreadsheet
— · 0 votes
Contents
  1. Numbers stored as text
  2. Errors and circular references
  3. Manual calculation mode
  4. Questions about SUM formula results
  5. Why does my SUM formula work in Excel but not in Google Sheets?
  6. Why is my SUM formula showing as text instead of a result?
  7. How do I fix a wrong total in a spreadsheet?
  8. Why does SUM return zero when the cells have numbers?

A SUM formula that is not working in a spreadsheet almost always points to the data, not the function: the range holds numbers stored as text, an error cell, a circular reference, or the file is set to manual calculation. The fix is to find which of those conditions applies to your sheet and correct it before trusting the total.

Numbers stored as text

Numbers stored as text are the leading reason a SUM range skips values. The SUM function ignores any cell it reads as text, so a column of imported figures can add up to zero. Look for values aligned to the left of the cell instead of the right, or a small warning marker. Convert the entries to real numbers, then the range adds correctly.

Errors and circular references

A range that includes an error cell returns an error instead of a total. Check whether any cell inside the range shows a value such as a divide-by-zero error. A circular reference breaks the result in a different way, because the formula depends on its own output. Trace which cells the SUM formula points to and confirm none of them sit inside the range being totaled.

Manual calculation mode

Calculation mode set to manual keeps an old result on screen after the source numbers change. The SUM formula is not wrong, but it is not recalculating when an input cell is edited. Switch the workbook back to automatic calculation or force a recalculate. Check the range itself as well, since an AutoSum range that stops short of the last row leaves figures out of the total.

Questions about SUM formula results

Why does my SUM formula work in Excel but not in Google Sheets?

A SUM formula that works in Excel but not in Google Sheets usually relies on something the other program reads differently. Text numbers, locale separators and reference syntax can all change how the range is read. Rebuild the formula with the range selector in the sheet you are using, so the references match that program.

Why is my SUM formula showing as text instead of a result?

A SUM formula shown as text means the cell is formatted as text or the entry is missing its equals sign. The formula is being read as characters rather than calculated. Format the cell as a number, delete the entry, and retype it with the equals sign at the start.

How do I fix a wrong total in a spreadsheet?

A wrong total in a spreadsheet is fixed by checking the range, the number format and the calculation mode in that order. Confirm the range covers every row you want, that the cells hold real numbers, and that the file recalculates automatically. Delete cells outside the intended range before you rebuild the SUM formula.

Why does SUM return zero when the cells have numbers?

SUM returns zero when every value in the range is stored as text, even if the figures look like numbers. The function adds numeric cells and ignores the rest, so a full column of text entries totals nothing. Convert the column to numbers and the SUM formula returns the expected result.

Read next

Latest articles