If based on multiple criteria and one OR

[lozzeroooni]

Hi there,

I'm using the following formula to provide a yes/no if all of those cells are filled in.

=IF((AND(I4>0,J4>0,L4>0,M4>0,T4>0,U4>0,V4>0,W4>0,Z4>0,AA4>0,AB4>0,AC4>0,AD4>0,AE4>0,AF4>0,AG4>0,AH4>0,AI4>0,AJ4>0,AK4>0,AL4>0,AM4>0,AN4>0,BH4>0,BU4>0)), "Yes", "No")

I'd like to change it slightly though, so it's all of the above but one of either L4 or M4 can be filled in but the rest of the cells have to be. I really can't work out how to do so though.

Any help would be greatly appreciated!

 

[jasonb75]

Like this

=IF((AND(I4>0,J4>0,OR(L4>0,M4>0),T4>0,U4>0,V4>0,W4>0,Z4>0,AA4>0,AB4>0,AC4>0,AD4>0,AE4>0,AF4>0,AG4>0,AH4>0,AI4>0,AJ4>0,AK4>0,AL4>0,AM4>0,AN4>0,BH4>0,BU4>0)), "Yes", "No")

 

[lozzeroooni]

Like this

=IF((AND(I4>0,J4>0,OR(L4>0,M4>0),T4>0,U4>0,V4>0,W4>0,Z4>0,AA4>0,AB4>0,AC4>0,AD4>0,AE4>0,AF4>0,AG4>0,AH4>0,AI4>0,AJ4>0,AK4>0,AL4>0,AM4>0,AN4>0,BH4>0,BU4>0)), "Yes", "No")

Like this

=IF((AND(I4>0,J4>0,OR(L4>0,M4>0),T4>0,U4>0,V4>0,W4>0,Z4>0,AA4>0,AB4>0,AC4>0,AD4>0,AE4>0,AF4>0,AG4>0,AH4>0,AI4>0,AJ4>0,AK4>0,AL4>0,AM4>0,AN4>0,BH4>0,BU4>0)), "Yes", "No")

Thanks Jason!