How to fix #VALUE! when adding cells in Excel
Formula (Excel and Google Sheets)
=SUM(B2:D2)Use SUM instead of +: =SUM(B2:D2) skips text such as "n/a", while =B2+C2+D2 returns #VALUE!.
- Result on the example
- one result per row in
E2, filled down toE6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2, filled down to E6. Change any input and the result updates, or edit the formula to experiment.
How it works
The + operator needs numbers on both sides, and text like "n/a", "-" or a lone space can't be turned into one, so =B2+C2+D2 returns #VALUE!. SUM skips text in a range and adds the numbers it finds. An empty cell counts as 0 either way, which is why Supplies works with both.
=SUM(B2:D2)Result5550
B2:D21 row × 3 cols: $1,850.00, $1,850.00, $1,850.00A range: 3 cells in one row.
SUM(B2:D2)5550SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:D21 row × 3 cols: $1,850.00, $1,850.00, $1,850.00
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(B2:D2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(B2:D2) | $5,550.00, $411.15, $159.98… | SUMPRODUCT also treats text as 0. |
| =IFERROR(B2+C2+D2,"Check row") | $5,550.00, Check row, Check row…Different result | Keeps the + formula but flags rows with text, so you notice them. Shows "Check row" for Utilities, Internet and Travel. |
Common mistakes
- Because SUM skips text silently, a number stored as text ("212") is left out without warning. Compare
=COUNT(B2:D2)with the number of cells. - A cell holding only a space looks empty but breaks +. Clear it, or let SUM handle it.
=SUM(B2+C2+D2)still errors: the + runs before SUM sees anything.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix #VALUE! when adding cells
E2, add up the three months, so that text such as "n/a" doesn't cause an error. Fill down to E6.Questions
How do I find the cell with text?
=ISTEXT(C3), or select the range and compare Count with Numerical Count in the status bar.
Why does a dash cause an error?
A typed "-" is text. Accounting formats show a zero as a dash, but those cells hold 0.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.