# Why does adding cells give a #VALUE! error in Excel

One of the cells holds text, such as a space or "n/a", so use SUM, which skips text: `=SUM(B2:B7)`.

**Excel:** `=SUM(B2:B7)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Item | Cost |  | Total |
| Paint | 86.5 |  |  |
| Brushes | 24 |  |  |
| Tape |  |  |  |
| Drop cloth | 18.75 |  |  |
| Primer | n/a |  |  |
| Rollers | 31.2 |  |  |

Formula in E1: `=SUM(B2:B7)`

Result: $160.45

## 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.

## Other ways

- `=B2+B3+B4+B5+B6+B7`: 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.

## 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".

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/adding-cells-gives-value-error