Excel SUM function is not working shows 0 but using Addition works

Question

In Excel, I'm at a loss (version 16.0, Office 365). I have several cells that are formatted as Number and have values greater than 0, but whenever I use the normal SUM() function on them, the result is always displayed as 0.0 rather than the actual sum. The amount is displayed appropriately when I use + in its place.

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.

Answer 1

The values in A1 and A2 are Text, which Excel is indicating (in a cryptic way).

SUM() ignores text values and produces a result of 0. Each value is first converted from text to a number using a direct addition formula before being added.

I hope this helps you.