How to set cell value to emptry if three other columns are empty too

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

Hi,

I have set a formula in three columns that is a total from other columns. The cell will be empty if the totals are zero.

Here is a sample:

In E1: =IF(SUM(A1:D1)=0,"",SUM(A1:D1)).
In J1: =IF(SUM(F1:I1)=0,"",SUM(F1:I1)).
In M1: =IF(SUM(K1:L1)=0,"",SUM(K1:L1)).

So far clear. Now I want to set a formula that fills the cell with a value, unless all three columns E, J and K are empty due to above formulas.
Otherwise there will be a calculation N1=E1-J1-M1.

Thank you for your help.

Bart
Office 365
 
Hi,

I have set a formula in three columns that is a total from other columns. The cell will be empty if the totals are zero.

Here is a sample:

In E1: =IF(SUM(A1:D1)=0,"",SUM(A1:D1)).
In J1: =IF(SUM(F1:I1)=0,"",SUM(F1:I1)).
In M1: =IF(SUM(K1:L1)=0,"",SUM(K1:L1)).

So far clear. Now I want to set a formula that fills the cell with a value, unless all three columns E, J and K are empty due to above formulas.
Otherwise there will be a calculation N1=E1-J1-M1.

Thank you for your help.

Bart
Office 365
Click to expand...
use "AND" with the original tests rather than looking at E1, J1, M1
which may not be numerical

=IF(AND(SUM(A1:D1)=0,SUM(F1:I1)=0,SUM(K1:L1)=0),"",E1-J1-M1) <not tested>
 
Back
Top