# Text in my formula is messing up the calculations

#### Special-K

##### Board Regular
As shown below, this formula adds " Days" after '0.5'

=IF(OR(ISBLANK(B89), ISBLANK(C89)),"", IF(OR(A89="CH",A89="LH",A89="PH",A89="EH"), 0.5*(C89-B89+1)&" Days",IF(OR .....

The problem is that this formula below messes up and won't calculate anything with that entry.
=SUM(SUMIF(A5:A93,{"CH"},D5:D93)*G111)

I'm sure there must be a fix ... can somebody please tell me what I'm doing wrong?

Thank you!

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Andrew Poulsom

##### MrExcel MVP
Excel won't add up text entries. Could you format the cell as eg 0.0" Days" and leave the &" Days" out of your formula?

#### Special-K

##### Board Regular
It doesn't seem to work.

This is my format below ... however I try to apply your suggestion I get formatting errors.

=IF(OR(ISBLANK(B89), ISBLANK(C89)),"", IF(OR(A89="CH",A89="LH",A89="PH",A89="EH"), 0.5*(C89-B89+1)&" Days",IF(OR(A89="CO",A89="LV",A89="PD",A89="ED"), C89-B89+1, IF(OR(A89="HC",A89="HL",A89="HP",A89="HE"), AA89&" Hrs",""))))

How should I apply your suggestion?

Thanks,
Kevin.

#### barry houdini

##### MrExcel MVP
You could try changing your SUMIF to this

=SUM(IF(A5:A93="CH",--LEFT(D5:D93,FIND(" ",D5:D93&" ")-1)))*G111

confirmed with CTRL+SHIFT+ENTER

#### tvkrys

##### Board Regular
houdini wrote
Excel won't add up text entries.

I seem to remember there being a blurb on adding text to cells and still being able to calculate on it in the Excel Articles section, but I don't have time to check it out and I may be wrong! It may have been that you need one cell for a concatination and one cell for the number and thats the cell you calculate on but I can't really remember... That might not work or you might rethink how your spreadsheet works but have you searched through those articles?

Just a suggestion
Krys

Replies
2
Views
72
Replies
0
Views
138
Replies
1
Views
73
Replies
2
Views
298
Replies
0
Views
49

1,113,993
Messages
5,545,377
Members
410,679
Latest member
rolandbianco