#VALUE error when you add text and values in Excel

Article translations Article translations
Article ID: 100825 - View products that this article applies to.
This article was previously published under Q100825
Expand all | Collapse all


In Microsoft Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error.


Although some functions correctly evaluate cells and ignore text strings, if you add the cells by using arithmetic operators such as addition (+), subtraction (-), multiplication (*), or division (/), an error value may occur.


Instead of using an individual mathematical operator, use its equivalent worksheet function instead:
SUM (adds)
PRODUCT (multiplies)
QUOTIENT (divides)
For example, if you type the following information in cells A1:A7 of a worksheet
   A1: 10
   A2: text
   A3: 20
   A4: =A1+A2+A3
   A5: =SUM(A1+A2+A3)
   A6: =SUM(A1,A2,A3)
   A7: =SUM(A1:A3)
the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30.


Article ID: 100825 - Last Review: January 18, 2007 - Revision: 3.1
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
kbprb KB100825

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com