Solved If AND Statement to Check in two Columns

AlteK December 22, 2012 at 12:42:26

Hi If 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.

DerbyDad03 December 22, 2012 at 16:12:32

✔ Best Answer

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.

AlteK 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.

DerbyDad03 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.

AlteK December 22, 2012 at 21:20:55

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

emory December 23, 2012 at 00:07:33

Thanks for your help 100% Really appricate it

emory December 23, 2012 at 00:11:03

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

DerbyDad03 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.

emory 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 thanks Is 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")

DerbyDad03 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 N OR W15 contains Y and X15 contains N For 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.

emory 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 help Many Thanks for your patience

DerbyDad03 January 10, 2013 at 13:19:27

Sometimes the answers are right in front of us! For simple explanations on how a function works or what arguments it needs, I often turn to Excel Help. To find out how other people are using the functions in real life, I'll often search forums and other "tip" sites. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.