Excel returns #VALUE! when it is asked to do arithmetic on something that is not a number. The formula is usually fine; one of the cells it reads is holding text.
Why it happens
The most common cause is a number that arrived as text, often from a copied report or an export. It sits on the left of its cell instead of the right, which is the easiest way to spot it.
A space is enough. A cell holding "1200 " with a trailing space is text, and any sum touching it returns #VALUE!. TRIM clears it.
Date arithmetic has the same problem: a date stored as text cannot be subtracted. DATEVALUE converts it, or Text to Columns can convert a whole column in one pass.
Some functions are stricter than others. SUM quietly skips text, while a plain A1+B1 refuses it, which is why one formula fails where another seems to work.
Questions
How do I find which cell is the problem?
Select the formula cell and use Formulas, Evaluate Formula. Stepping through shows the exact point where the text appears.
How do I convert a whole column at once?
Copy an empty cell, select the column, then Paste Special with Multiply. Every value gets multiplied by nothing and comes back as a number.
Can I make the formula ignore it?
IFERROR hides the error but not the cause: the figure is still missing from the total. It is nearly always better to fix the data.
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.
