Results 1 to 4 of 4
Thread: equation with text (exel 2003)

20050727, 14:43 #1rlbroermanGuest
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?

20050727, 14:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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>

20050727, 16:54 #3rlbroermanGuest
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.

20050727, 17:37 #4
 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:E1A2:E2),0,A1:E1A2:E2))
</code>Legare Coleman