Tosagua
asked on
Excel, IF statement with AND, OR - Two Columns. Nine Combinations
We need to identify NAFTA shipments, with a formula inserted into a spreadsheet.
I have tried a number of IF statements with OR and AND, but I cannot get it to work.
We have:
3 countries = 9 combinations
Column A = Origin Country
Column B = Destination Country
What we are trying to do:
If the value in A1 = USA, MEX, or CAN
and the value in B1 = USA, MEX, or CAN, then C1 returns "NAFTA".
IF(OR(A1="USA",A1="MEX",A1 =CAN")
AND
IF(OR(B1="USA",B1="MEX",B1 ="CAN")
then, "NAFTA"
otherwise, "" )
Any advise that you can provide is appreciated.
Tosagua
I have tried a number of IF statements with OR and AND, but I cannot get it to work.
We have:
3 countries = 9 combinations
Column A = Origin Country
Column B = Destination Country
What we are trying to do:
If the value in A1 = USA, MEX, or CAN
and the value in B1 = USA, MEX, or CAN, then C1 returns "NAFTA".
IF(OR(A1="USA",A1="MEX",A1
AND
IF(OR(B1="USA",B1="MEX",B1
then, "NAFTA"
otherwise, "" )
Any advise that you can provide is appreciated.
Tosagua
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An example with equation from my comment
NAFTA.xls
NAFTA.xls
ASKER
Another learning experience.
I appreciate everyone's help, however 'byundt' saved me from making a simple mistake (USA to USA is not NAFTA).
Part of the difficuly was that the cells were formatted as text, and when the format was changed to general, there was srtill no change.
Fortunately, I found this comment on EE,
"by: bouscal Posted on 2010-12-13 at 09:34:43ID: 34336238 Rank: Master
After you change the formatting to General from Text you may also need to select the cell, hit F2 to activate it then Enter for it to calculate. Excel doesn't always like to apply formatting.
Thank you for all of your assistance.
Tosagua
I appreciate everyone's help, however 'byundt' saved me from making a simple mistake (USA to USA is not NAFTA).
Part of the difficuly was that the cells were formatted as text, and when the format was changed to general, there was srtill no change.
Fortunately, I found this comment on EE,
"by: bouscal Posted on 2010-12-13 at 09:34:43ID: 34336238 Rank: Master
After you change the formatting to General from Text you may also need to select the cell, hit F2 to activate it then Enter for it to calculate. Excel doesn't always like to apply formatting.
Thank you for all of your assistance.
Tosagua
ASKER
I see now, how this should work, but it doesn't.
It always returns "FALSE", regardless of the combination.
See attached.
Tosagua