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

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:

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

2. Convert only real text numbers.

=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…):

=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, a free library of Google Sheets error fixes (I run it). There's also a 76-second video walkthrough on the SheetError YouTube channel.

This article was created with the help of AI.