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!
 

Some videos you may like

Excel Facts

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

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
 

Watch MrExcel Video

Forum statistics

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