Why does adding cells give a #VALUE! error in Excel
Formula (Excel and Google Sheets)
=SUM(B2:B7)One of the cells holds text, such as a space or "n/a", so use SUM, which skips text: =SUM(B2:B7).
- Result on the example
- $160.45 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
The + operator tries to turn every cell into a number, and a space or "n/a" can't be one, so =B2+B3+… fails. SUM skips text in a range and adds only the numbers, which gives the total of the four real costs.
=SUM(B2:B7)Result160.45
B2:B76 rows × 1 col: $86.50, $24.00, " ", $18.75, …A range: 6 cells in one column.
SUM(B2:B7)160.45SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:B76 rows × 1 col: $86.50, $24.00, " ", $18.75, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(B2:B7). We ran it in Google Sheets mode on the same data and it returns $160.45.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =B2+B3+B4+B5+B6+B7 | #VALUE!Different result | The formula that fails. Returns #VALUE! because B4 holds a space and B6 holds "n/a". |
Common mistakes
- A cell with only a space looks empty. Find them with
=LEN(B4), which returns 1, and clear them. - SUM skipping "n/a" also means that item's cost is missing from the total. Fill it in when you know it.
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.
Why does adding cells give a #VALUE! error
E1, total the costs in B2:B7 without getting #VALUE! from the cells that hold text.Questions
How do I find which cell causes the error?
Use Formulas > Evaluate Formula, or check each cell with =ISTEXT(B2).
Does SUM skip numbers stored as text too?
Yes, which can make the total too small. See "Why is my SUM formula returning 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.