=SUM(A2:A10) instead of =A2+A3+A4 The #VALUE! error means a formula received the wrong data type (e.g., trying to do arithmetic on a text cell). Fix by using SUM instead of the + operator, or converting text numbers using VALUE().
How to Fix the #VALUE! Error in Excel & Google Sheets
The #VALUE! error indicates a Data Type Mismatch—the formula expected a number, date, or logical value, but received an incompatible text string.
🔍 How to Fix #VALUE! Errors
Fix 1: Replace + Operators with SUM()
If cell B2 contains $100 and cell B3 contains "N/A" or a space " ":
❌ =B2 + B3$\rightarrow$ Throws#VALUE!✅ =SUM(B2:B3)$\rightarrow$ Returns$100.00(SUM safely ignores text)
Fix 2: Convert Numbers Stored as Text
If a cell contains a numeric string like "$1,200.00" exported as text:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))Fix 3: Date Arithmetic with Proper Date Formats
Subtracting text dates like "Aug 20" results in #VALUE!. Always ensure dates are recognized as true serial dates using DATE(year, month, day):
=DATE(2026, 8, 20) - DATE(2026, 1, 1)Frequently Asked Questions
Why does A2+B2 give #VALUE! when SUM(A2:B2) works?
The + operator forces strict mathematical addition; if one cell contains text or a space, it fails. The SUM() function automatically ignores text and blanks, adding only numbers.