Formula for multiple conditions

[deb45]

Hello,

I need a formula that satisfies multiple conditions.

Details given in attached file

Regards,
Deb

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[deb45]

Thanks a lot.

Apparently it seems ok.

If any issue arises i ll let u know.

Regards,
Deb

 

[rahulshewale1]

Hii ,


See if this ok ?
cell E4;

=IF(AND(B4<>0,C4<>0),F4+G4+H4+I4+L4+M4,IF(AND(B4<>0,C4=0),F4+G4+H4+I4+J4+K4+L4+M4,IF(AND(B4=0,C4<>0),F4+L4+M4,IF(AND(B4=0,C4=0),F4+J4+K4+M4))))

Regards
Rahul shewale

 

[deb45]

Dear Narayank,

I have checked the file. You have tested two conditions at beginning.

Then you tested only one condition.

Each item to be checked for both the conditions always.

Regards,
Deb

 

[deb45]

Dear Rahul,

i think your formula will exactly fit my requirements.

Anyhow let me check.

Thanks

Deb

 

[NARAYANK991]

Hii ,


See if this ok ?
cell E4;

=IF(AND(B4<>0,C4<>0),F4+G4+H4+I4+L4+M4,IF(AND(B4<>0,C4=0),F4+G4+H4+I4+J4+K4+L4+M4,IF(AND(B4=0,C4<>0),F4+L4+M4,IF(AND(B4=0,C4=0),F4+J4+K4+M4))))

Regards
Rahul shewale

Hi ,

The AND tests in the second , third and fourth IF statements are redundant.

Narayan

 

[bosco_yip]

Another option

In E4, copied down :

=IF((B4=0)*(C4=0),F4+J4+K4+M4,SUM(F4:K4,M4)-(B4<>0)*(C4<>0)*(J4+K4-L4)-(B4=0)*(C4<>0)*SUM(G4:K4,-L4))

Regards
Bosco

 

[bosco_yip]

.........
=IF(AND(B4<>0,C4<>0),F4+G4+H4+I4+L4+M4,IF(AND(B4<>0,C4=0),F4+G4+H4+I4+J4+K4+L4+M4,IF(AND(B4=0,C4<>0),F4+L4+M4,IF(AND(B4=0,C4=0),F4+J4+K4+M4))))
Regards
Rahul shewale

Hi Rahul shewale,

In the 2nd IF statement, "...+L4..." (highlighted in red color) is not required.

=IF(AND(B4<>0,C4<>0),F4+G4+H4+I4+L4+M4,IF(AND(B4<>0,C4=0),F4+G4+H4+I4+J4+K4+L4+M4,IF(AND(B4=0,C4<>0),F4+L4+M4,IF(AND(B4=0,C4=0),F4+J4+K4+M4))))

Regards
Bosco