Numbers stored as text, and the sums that ignore them
A column that looks like numbers but totals to zero, or misses rows. The values are text, and most of Excel quietly skips them.
Why it happens
The quickest check is alignment: numbers sit on the right of a cell by default, text on the left. If figures that should sit on the right are on the left instead, they are text.
SUM skips text without complaint, which is why the total is wrong rather than an error. COUNT skips them too, while COUNTA counts them, and the difference between the two shows how many are affected.
Exports are the usual source, often carrying a trailing space or a non-breaking space from a web page. TRIM removes ordinary spaces; SUBSTITUTE with CHAR(160) removes the non-breaking kind.
Multiplying by 1, or adding 0, converts text to a number where the text really is numeric. VALUE does the same job more clearly.
Questions
How do I convert a whole column quickly?
Select it, then Data, Text to Columns, and press Finish without changing anything. Excel re-reads every cell.
What is the little green triangle?
Excel noticing the same thing. Selecting the cells and choosing Convert to Number from the warning icon fixes them in one go.
Why does my VLOOKUP return #N/A on a code I can see?
One side is text and the other a number. They look identical and do not match. Converting either side settles it.
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.
