# How to fix #VALUE! when adding cells in Excel

Use SUM instead of +: =SUM(B2:D2) skips text such as "n/a", while =B2+C2+D2 returns #VALUE!.

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Expense | Jan | Feb | Mar | Q1 total |
| Rent | 1850 | 1850 | 1850 |  |
| Utilities | 212.4 | n/a | 198.75 |  |
| Internet | 79.99 | 79.99 | - |  |
| Supplies | 64.2 | 118.5 |  |  |
| Travel |  | 640 | 385 |  |

Formula in E2, filled to E6: `=SUM(B2:D2)`

Result: $5,550.00; $411.15; $159.98; $182.70; $1,025.00

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

## Other ways

- `=SUMPRODUCT(B2:D2)`: SUMPRODUCT also treats text as 0.
- `=IFERROR(B2+C2+D2,"Check row")`: 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.

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

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/value-error-when-adding-cells