Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. #VALUE error when you add text and values in Excel

#VALUE error when you add text and values in Excel

View products that this article applies to.

Symptoms

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

↑ Back to the top

Cause

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.

↑ Back to the top

Workaround

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.

↑ Back to the top

Keywords: kbprb, KB100825

↑ Back to the top

Article Info
Article ID : 100825
Revision : 4
Created on : 1/18/2007
Published on : 1/18/2007
Exists online : False
Views : 446