Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel, IF statement with AND, OR - Two Columns. Nine Combinations

Posted on 2012-03-23
6
Medium Priority
?
387 Views
Last Modified: 2012-03-26
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
0
Comment
Question by:Tosagua
6 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 200 total points
ID: 37758176
=IF(AND(OR(A1="MEX",A1 = "CAN",A1="USA"),OR(B1="MEX",B1 = "CAN",B1="USA")),"NAFTA","")
0
 

Author Comment

by:Tosagua
ID: 37758326
als315,

I see now, how this should work, but it doesn't.
It always returns "FALSE", regardless of the combination.
 See attached.

Tosagua
0
 
LVL 1

Assisted Solution

by:coogsirc
coogsirc earned 200 total points
ID: 37758375
I created a range called "nft" with the 3 country codes and plugged this formula into C1.
=IF(AND(MATCH(A1,nft,0),MATCH(B1,nft,0)),"nafta")

If a1 or b1 isn't in the list c1 returns #N/A.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 81

Accepted Solution

by:
byundt earned 1600 total points
ID: 37759603
If both countries are the same, I assume the answer should not be NAFTA. If so, then you might consider a formula like:
=IF((A2<>B2)*(SUMPRODUCT(--(A2:B2={"USA";"MEX";"CAN"}))=2),"NAFTA","")

Brad
NAFTA-Q27646241.xlsx
0
 
LVL 40

Expert Comment

by:als315
ID: 37760075
An example with equation from my comment
NAFTA.xls
0
 

Author Closing Comment

by:Tosagua
ID: 37767044
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While Plesk offers many potential benefits to website administrators, including compatibility with Windows Server and other leading technologies, the company has also been working to differentiate it from other control panels for content management…
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question