Can You Add Text and Numerics Without Getting "#VALUE!" Error?

[ralph76]

Hello:

When I try to sum rows or columns containing a mix of numerics and text, I get a "#VALUE!" error. Is there any way to have the sum function ignore the text within the range and just add the numerics?

Many thanks,
Ralph

 

[Colin Legg]

Hi Ralph,

Please can you post some sample data together with the desired result?
Are the numbers and text mixed up together in the same cells or are they separate?

Thanks

 

[ralph76]

Hi Colin:

Sorry, I should have given an example. Data is in Row 5, with the following cell inpurts:

A5 = 25
B5 = 16
C5 = --- <= these are 3 dashes to denote no data
D5 = 75
E5 = 209

I'd like to get the result of A5+B5-C5+D5-E5. Also please note that the "---" will not always be in the same cell.

Hi Ralph,

Please can you post some sample data together with the desired result?
Are the numbers and text mixed up together in the same cells or are they separate?

Thanks

 

[Colin Legg]

Hi Ralph,

Would this suit you?

=SUM(A5,B5,D5)-SUM(C5,E5)

 

[lenze]

WIll not SUM(A5:E5) work. excel should ignore text cells
lenze

 

[ralph76]

No, because it is not a straight sum. It is a mixture of addition and subtraction within the range. When it was totally addition, Excel ignored the text, but once I introduced the subtraction, it returned an error. Thanks, but I think Colin has solved the issue.

WIll not SUM(A5:E5) work. excel should ignore text cells
lenze

 

[ralph76]

Oh, man, so simple! Of course, splitting out the formula into 2 straight "sums" functions allows Excel to ignore the text. I thinnk this is a case of not being able to see the forest through the trees. Many thanks, Colin.

Hi Ralph,

Would this suit you?

=SUM(A5,B5,D5)-SUM(C5,E5)