# 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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### 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
218
Replies
0
Views
843
Replies
7
Views
90
Replies
1
Views
256
Replies
2
Views
2K

1,141,028
Messages
5,703,823
Members
421,318
Latest member
cg_cartoonexcel

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