Cell to remain blank if another cells contain no data

[Detectiveclem]

I have the following formula entered in cell E7 =IF(OR(ISBLANK(C7),ISBLANK(D7)),"",IF(D7-C7<0, 1+D7-C7, D7-C7))
and have another formula in cell H7 =IF(OR(ISBLANK(F7),ISBLANK(G7)),"",IF(G7-F7<0, 1+G7-F7, G7-F7))
and also another formula in cell K7 =IF(OR(ISBLANK(I7),ISBLANK(J7)),"",IF(J7-I7<0, 1+J7-I7, J7-I7))

I now want another formula in cell L7 which will sum the totals of E7, F7 & J7, but if none of the cells E7, F7 or K7 contain any data I also want L7 to remain blank.

I have tried to emulate the first set of formulas to allow me to do this but every time I don’t seem to get it to work, can anyone please help.

 

[Rick Rothstein]

I have the following formula entered in cell E7 =IF(OR(ISBLANK(C7),ISBLANK(D7)),"",IF(D7-C7<0, 1+D7-C7, D7-C7))
and have another formula in cell H7 =IF(OR(ISBLANK(F7),ISBLANK(G7)),"",IF(G7-F7<0, 1+G7-F7, G7-F7))
and also another formula in cell K7 =IF(OR(ISBLANK(I7),ISBLANK(J7)),"",IF(J7-I7<0, 1+J7-I7, J7-I7))

I now want another formula in cell L7 which will sum the totals of E7, F7 & J7, but if none of the cells E7, F7 or K7 contain any data I also want L7 to remain blank.

I have tried to emulate the first set of formulas to allow me to do this but every time I don’t seem to get it to work, can anyone please help.

Give this a try...

=IF(E7&F7&J7="","",SUM(E7,F7,J7))

 

[Fluff]

How about
=IF(OR(E7="",F7="",J7=""),"",E7+F7+J7)

 

[Detectiveclem]

Hi Rick, works perfectly. Thank you for your help.

 

[Detectiveclem]

Hi Fluff, I couldn't get this to work for some reason, but managed with Rick's solution. Thank you anyway

 

[Rick Rothstein]

Hi Rick, works perfectly. Thank you for your help.

I like my concatenation test better than Fluff's multiple OR's, but his use of direct adding rather than my SUM function call is probably better. So, combining our two functions, this is what I would recommend...

=IF(E7&F7&J7="","",E7+F7+J7)

 

[Detectiveclem]

Thank you Rick, this works fine, however my next formula has been affected, perhaps if you could assist again, or if you prefer I can log it as another question.

I am using the following formula to look down A7:A61 which contains dates and for example if the date is the 1^stthen it looks at the range L7:L61 and sums everything relating to the 1^stday of the month. My data is formatted to be a number to 2 decimal points.

=SUMPRODUCT((DAY($A$7:$A$61)=1)*$L$7:$L$61)

For some unknown reason this formula shows #VALUE ! Can someone assist me with this problem? Thank you

 

[Fluff]

How about
=SUMPRODUCT(--(DAY($A$7:$A$61)=1),$L$7:$L$61)

 

[Detectiveclem]

Thank you Fluff, this works perfectly, but again if the result is zero because nothing was connected to the date how do I get it to show a blank cell rather than 0.00?

Thanks, Paul

 

[Drrellik]

with your workbook open click on File, then down to Options, then Advanced, scroll from there about 3/4 of the way down and look for a check box Show a Zero in cells that have a zero value un-check it.