Cell to remain blank if another cells contain no data

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
320
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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))
 
Upvote 0
Hi Fluff, I couldn't get this to work for some reason, but managed with Rick's solution. Thank you anyway
 
Upvote 0
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)
 
Upvote 0
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 1stthen it looks at the range L7:L61 and sums everything relating to the 1stday 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
 
Upvote 0
How about
=SUMPRODUCT(--(DAY($A$7:$A$61)=1),$L$7:$L$61)
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top