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

#### ralph76

##### Board Regular
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Colin Legg

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
Hi Ralph,

Would this suit you?
Code:
``=SUM(A5,B5,D5)-SUM(C5,E5)``

#### lenze

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

#### ralph76

##### Board Regular
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

##### Board Regular
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?
Code:
``=SUM(A5,B5,D5)-SUM(C5,E5)``

Replies
21
Views
652
Replies
4
Views
130
Replies
4
Views
137
Replies
2
Views
131
Replies
2
Views
334

1,191,097
Messages
5,984,650
Members
439,898
Latest member
Zack0611

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back