Search code examples
excel

Excel SUM function is not working (shows 0), but using Addition (+) works

I'm stumped in Excel (version 16.0, Office 365). I have some cells that are formatted as Number, all with values > 0, but when I use the standard SUM() on them, it always shows a result of 0.0 instead of the correct sum. When I use + instead, the sum shows correctly.

For example:

  • SUM(A1:A2) shows 0.0
  • A1 + A2 shows 43.2

I don't see any errors or little arrows on any of the cells.

Solution

  • Excel is telling you (in an obscure fashion) that the values in A1 and A2 are Text.

    The SUM() function ignores text values and returns zero. A direct addition formula converts each value from text to number before adding them up.