Text in my formula is messing up the calculations

[Special-K]

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!

 

[Andrew Poulsom]

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]

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]

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]

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