Numbers that arrive as text will not add up, and SUM quietly ignores them. This turns them back into real numbers you can total.
The formula
=VALUE(TRIM(A2)) in column B
What each part does
TRIM
Strips the spaces that usually cause this.
VALUE
Converts what is left into a number.
How it works, and what to watch for
VALUE converts the text to a number; TRIM first clears any spaces that would otherwise trip it up.
Numbers stored as text are the single most common reason a SUM comes out too low or a lookup fails without an error. They sit on the left of the cell, and often wear a little green triangle.
If they came from the web, a non-breaking space may be hiding in there. SUBSTITUTE for CHAR(160) clears that before VALUE runs.
Questions
How do I tell if my numbers are really text?
They line up on the left of the cell rather than the right, and SUM ignores them. A green triangle in the corner is the other giveaway.
Is there a way without a formula?
Select the range, click the green triangle warning, and choose Convert to Number. The formula is better when the text keeps arriving from an import.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
