IF statement to combine 7 columns

Question

I have 7 columns that have a yes or no in them (N2-T2). I need an equation that will place a 1 in "AI2" if there is a yes in any of the N-T cells. In my previous work with Excel I have only used the colon in an equation if I am adding the cells. Is this correct or does it have more use?

I tried the equation below and I get an error; #Value!

=IF(N2:T2="yes",0,1)

I also tried this one, however Excel just kept telling me that it wrong. I just tried the first two columns in this example to see if I could make it work.

=IF(N2="yes",IF(O2="yes"),0,1)

Answer 1

The formula you are looking for is

=IF(ISNA(MATCH("yes",N2:T2,0)),0,1)

Answer 2

you can use the logical OR function

=IF(OR(A1="YES";B1="YES";C1="YES";D1="YES";E1="YES";F1="YES";G1="YES");1;0)

This way you are more flexible on what you want to test (4th column "yes" OR 5th column "foo", etc.)

Likewise there is an AND(logical; logical; ...) function as well. Try to avoid cascaded IF's - they are hard to read and debug.