Text in my formula is messing up the calculations

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Apr 18, 2006
Messages
63
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jan 23, 2006
Messages
135
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top