How to Fix #VALUE! Errors in Math (+ vs SUM) & Text Slicing

Understand the input instead of hiding the error

Text placeholders and missing delimiters are two possible causes of #VALUE!, not a comprehensive diagnosis. Check the inputs and expected types before changing the formula. A formula returning a number is not necessarily reporting complete data.

SUM over a cell range ignores text, including text-producing numeric strings, but actual error values are not ordinary text labels. A literal N/A label and an actual #N/A error have different meanings. Converting or skipping data needs an explicit rule.

A subtotal of available numeric values

B2:B4 contains 120, TBD and 180. SUM returns 300 by skipping TBD. Label this a numeric subtotal rather than treating the missing amount as known zero. If every value is text, SUM returns zero despite having no numeric observations.

=SUM(B2:B4)

Require all planned amounts before reporting a total

G3 requires three numeric cells. It reports Incomplete numeric inputs until TBD is replaced by a number. A genuine numeric zero is accepted. This fixed range contains three planned amounts; update the range if that plan changes.

=IF(COUNT(B2:B4)<>ROWS(B2:B4),"Incomplete numeric inputs",SUM(B2:B4))

Keep pending quantity separate from a free or zero-quantity order

G4 requires numeric price and quantity, a nonnegative price and a nonnegative whole-unit quantity. Pending returns a message, not a false zero total. This example sells whole units; fractional quantities require a different policy. A zero price or zero quantity is valid and produces zero.

=IF(COUNT(D2:E2)<>2,"Enter numeric price and quantity",IF(OR(D2<0,E2<0,E2<>INT(E2)),"Use nonnegative price and whole quantity",D2*E2))

Extract a prefix only when the delimiter exists

G5 extracts text before the first hyphen. ADMIN has no hyphen and stays unchanged; ENG-402 becomes ENG. Multiple hyphens use the first, and a leading hyphen produces an empty prefix. Blank and numeric codes return explicit messages.

This illustrates a parsing policy, not input correction: if an empty prefix is invalid for your codes, add a rule for it. Spaces are preserved.

To test a leading-hyphen numeric-looking text code, enter ="-402". Typing -402 directly can create a negative number rather than text.

=IF(COUNTBLANK(A2)>0,"Enter a code",IF(NOT(ISTEXT(A2)),"Enter a text code",IF(ISNUMBER(FIND("-",A2)),LEFT(A2,FIND("-",A2)-1),A2)))

Further reading

• Microsoft Support — How to correct a #VALUE! error