# Solved If AND Statement to Check in two Columns

December 22, 2012 at 09:48:33
Specs: Windows 7

 If I have Y in A1 and Blank in B1 then Blank in A2 and Y in B2 How do I check with IF AND statement if there is a Y in either column and if there is Return YMany Thanks

See More: If AND Statement to Check in two Columns

#1
December 22, 2012 at 12:42:26

 HiIf you already have cols A and B populated with Y's and blanks you can do this by creating either 1 or 2 new columns C / D. One additional column would signify a record that has at least one Y this way=if(OR(A1="Y", B1="Y"),"Y","") ```Col A Col B "Y" Present? Y Y Y Y - Y - Y Y - - Y - Y - - - Y Y - - Y Y Y ```If you want to replicate cols A and B you would have the same as the above in both cols C and D```Col A Col B New A New B Y Y Y Y Y - Y Y - Y Y Y - - Y - Y Y - - - Y Y Y - - Y Y Y Y ```If you want to overwrite cols A and B you would need a Macro to do that although it is usually not a good idea to overwrite input data.

#2
December 22, 2012 at 16:12:32

 Here's a sneaky way to get the same results:=IF(COUNTIF(A1:B1,"Y"),"Y","")Pop Quiz: Why does that work?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#3
December 22, 2012 at 16:38:02

 The IF statement evaluates TRUE or FALSE if the COUNTIF statement is met (TRUE if there is a "Y" or FALSE if there is no "Y")Okay, now tell me the real reason.

Related Solutions

#4
December 22, 2012 at 20:51:35

 But COUNTIF returns a numerical value, not TRUE or FALSE. Why would the IF evaluate to TRUE or FALSE based on the value that the COUNTIF returns?Here's a hint:I'm sure you know that the ROW() function simply returns the number of the Row it is used in. Ok, so knowing that fact, enter this in A1 and drag it down to A5:=IF(ROW()-3,TRUE,FALSE)The results might lead you to something interesting (weird?) about the inner workings of Excel and explain why the COUNTIF function works in the above situation.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#5
December 22, 2012 at 21:20:55

 If the result = 0 it equates to FALSE. Interesting.

#6
December 23, 2012 at 00:07:33

 Thanks for your help 100% Really appricate it

#7
December 23, 2012 at 00:11:03

 Great FAB Brilliant Thanks very much. This is the best site ever

#8
December 23, 2012 at 04:24:30

 Excel considers 0 to be FALSE and any non-zero value, positive or negative, to be TRUE. Therefore, if there is a "Y" in column A or B or both, the COUNTIF will return a non-zero value and the IF will evaluate to TRUE.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#9
January 10, 2013 at 09:04:12

 Hi I have been through and added col as suggested and now it works with "=IF(OR(W15="Y",X15="N"),"N","Y") Many thanksIs it possible for you please tell me the difference between the above and below. I have searched HELP and the forums "=IF(AND(W15="Y",X15="N"),"N","Y")

#10
January 10, 2013 at 10:14:59

 The Excel help files explain both of these functions very well. I quote:OR: Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.AND: Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.In other words...OR will return TRUE if any one or more of the arguments are TRUE.AND will return TRUE only if all of the arguments are TRUE.For the OR to return TRUE:W15 contains Y OR X15 contains NOR W15 contains Y and X15 contains NFor the AND to return TRUE: W15 contains Y AND X15 contains N.Therefore, you will see no difference between your formulas if both W15 contains Y and X15 contains N since both the OR and the AND functions will be TRUE.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#11
January 10, 2013 at 10:26:57

 Brilliantly explained I thought I was going mad I have discovered the problem I was getting the wrong answer because the data wasnt clean I did check the forums for answers and other excel web sites BUT no I didnt ask Excel helpMany Thanks for your patience