# Fix "Function ADD parameter 2 expects number values" (#VALUE!) in Google Sheets

You type `=A2+B2`, and instead of a total the cell shows **#VALUE!**. Hover it and the tooltip says:

> Function ADD parameter 2 expects number values. But 'pcs' is a text and cannot be coerced to a number.

The formula parsed and the function name is fine. Sheets wanted a number and got **text it can't turn into a number**. Here's how to reproduce it, how to tell it apart from look-alike errors, and three fixes that don't involve IFERROR.

## Reproduce it in 30 seconds

|  | A (qty) | B (unit) | C |
| --- | --- | --- | --- |
| 2 | 10 | pcs | `=A2+B2` → **#VALUE!** |
| 3 | 10 | 2 | `=A3+B3` → 12 |

Same operator, different input type. Two more ways to hit the same error:

- `=VALUE("pcs")` → #VALUE! (VALUE only converts text that already looks like a number, date or time)
- `=DATEVALUE("Widget")` → #VALUE!

And the case that surprises people: type `'10` (apostrophe, so it's text) in G2, then enter `=G2+1` in G3. You get **11**. Arithmetic *will* coerce a text number like `'10`. It will not coerce `pcs`. That is the whole difference between C2 failing and G3 working.

## Why SUM doesn't complain but + does

`=SUM(A2:B2)` returns **10**, not an error. SUM skips text silently. The `+` operator doesn't. So if a total is "too low" with SUM and errors with `+`, you have the same root cause: a text cell in a numeric column.

## It is not one of these

- **#N/A** is a lookup/filter miss. Nothing is being looked up here.
- **#NAME?** is an unknown function name (`VLOKUP`). Nothing is misspelled here; the + operator (ADD) rejected its argument.
- **#ERROR! / Formula parse error** means the formula couldn't be read at all (a missing bracket or quote, or commas vs semicolons for your locale). This formula parsed.
- **#NUM!** is a number outside a function's domain (e.g. `SQRT(-1)`). Here the type was wrong before the domain mattered.

## Fixes

**1\. Keep units out of the math.** The quantity column holds numbers only. Put the unit in the header, or add it after the calculation:

```plaintext
=A2&" pcs"      → "10 pcs" (text, for display only)
```

**2\. Convert only real text numbers.**

```plaintext
=VALUE(G2)                                   → 10 when G2 is '10
=VALUE(REGEXEXTRACT(SUBSTITUTE("1,200.5 pcs",",",""),"[0-9]+(?:\.[0-9]+)?"))   → 1200.5, keeps decimals and drops thousands commas
```

The regex takes only the first number, and signs or leading-dot decimals are read wrong without an error (-3.5 becomes 3.5, .5 becomes 5). On comma-decimal locales, swap the separators; see <https://sheeterror.com/tutorials/formula-parse-locale/> .

Don't wrap every #VALUE! in VALUE and hope; `VALUE("pcs")` still fails.

**3\. Guard the type when a column may legitimately hold text** (n/a, pending…):

```plaintext
=IF(ISNUMBER(B2), A2+B2, "")
```

## Why not IFERROR?

`IFERROR(A2+B2, 0)` makes the cell look fine, but it also hides #REF! and #DIV/0! if the formula breaks differently later, and it turns "someone typed pcs in the qty column" into a silent wrong total. Fix the input or test the type with ISNUMBER.

---

*Originally published on* [*SheetError*](https://sheeterror.com/tutorials/value-error/)*, a free library of Google Sheets error fixes (I run it). There's also a 76-second video walkthrough on the* [*SheetError YouTube channel*](https://www.youtube.com/@sheeterror)*.*

*This article was created with the help of AI.*