Results 1 to 4 of 4
  1. #1
    rlbroerman
    Guest

    equation with text (exel 2003)

    I want to have an equation that will sum a row of numbers but sometimes the cells in the row have text in them and I get and error "#value!". How do I get the equation to ignore the text. Can you help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: equation with text (exel 2003)

    The SUM function ignores text values; a formula with + will result in #VALUE if there are text values:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>1</td><td align=right>12</td><td>Excel</td><td align=right>23</td><td>Word</td><tr><td align=center>2</td><td align=right>

  3. #3
    rlbroerman
    Guest

    Re: equation with text (exel 2003)

    Actually I did not give you the complete story. I have numbers in a row that I need to subtract from numbers in the row above and then total the result. Still with text possibly in any cell that needs to be ignored.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: equation with text (exel 2003)

    OK, but you still havent given us the complete story. If the numbers are in A1:E2, and you want to add the numbers in A1:E1 and subtract the numbers in A2:E2, then the following formula will work:

    <code>
    =SUM(A1:E1)-SUM(A2:E2)
    </code>

    However, the part of the story that you still didn't give us is, what do you want to do if A1 contains a number and A2 contains text? Does the number in A1 participate in the calculation, or is it left out? If the number in A1 is left out of the calculation in that case, then you could use the following array formula (Hold down Ctrl+Shift when you press enter to confirm the formula):

    <code>
    =SUM(IF(ISERROR(A1:E1-A2:E2),0,A1:E1-A2:E2))
    </code>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •