Nested IF Formula Help

[PMC11]

I have a set of data which I need to sort based on some relatively simple criteria. The difficulty I am having is how to capture the interdepencies of the logic of the sort criteria. Written in plain English, I am trying to do the following.

-If a record has an email address, categorise as "Evite"
-If there is no email address, but there is a mailing address, then sort on country and categorise as either "Mail - Canada", "Mail - USA", or "Mail - International".
-If there is no email or mail address, categorise as "No Contact Info"
-If there are notes in the data that indicate this person does not want to be contacted, cateogise as "Do not invite".
-If someone has a note saying "no email" but a mailing address is present, ignore this note.

Here is a snap of my data:

Pasteboard — Uploaded Image

Here is my formula so far:
{=IF(COUNTA(D2:F2),"Do not invite",IF(COUNTA(G2:I2),"Check SC",IF(COUNTA(K2),"Evite",IF(COUNT(SEARCH($U$2,M2)),"Mail - Canada",IF(COUNT(SEARCH($U$3,M2)),"Mail - USA",IF(COUNT(SEARCH(Countries,M2)),"Mail - International",IF(COUNTA(K2:M2),"","No Contact Info")))))))}

This formula is correct in most cases. Where I'm failing is at the last rule. I cannot work out how to get the formula to ignore the "No Email" note if there is a mailing address. How could I do this? This is the issue that I need to solve the most and would really appreciate any help with.

Another inelegant solution in this formula so far is that for the categorisation of "Mail - International", I am checking the value of the address country cell against a range of all the countries in the world that aren't Canada or the USA, when really I just need to check if that cell does not equal USA or Canada. However, when I tried this it interfered with the formula and began returning "No Contact Info" or "Evite" rather than "Mail - International". Thoughts?

Note I am using Excel 2010.

Thanks in advance.

 

[Snakehips]

I'm glad to hear it works ok bar the one circumstance. Try the below as I think that will fix it.

=IF(COUNTA(D2:E2),"Do not invite",IF(COUNTA(G2:I2),"Check SC",IF(AND(COUNTA(K2),NOT(COUNTA(F2))),"Evite",IF(COUNT(SEARCH($U$2,M2)),"Mail - Canada",IF(COUNT(SEARCH($U$3,M2)),"Mail - USA",IF(COUNTA(M2),"Mail - International",IF(AND(COUNTA(F2),COUNTA(K2)),"Do Not Invite","No Contact Info")))))))

When trying to assist you often do not share the original poster's clear appreciation of what they have and what they want to achieve. Sometimes the picture becomes clearer only bit by bit.
No rocket science here in using COUNTA for checking the presence or not of data. The key is getting the IF statements in the correct order and making sure that working left to right, the 'true' outcomes are mutually exclusive and so no outcome wrongly precludes outcomes to the right. Always a danger of having to make formula more complex or impossible, if putting stuff in wrong order. Eg the added test in blue above is just checking for a 'No Email' note and the existence of an email address. The lack of a mailing country is already established in the preceding tests.
I am conscious that the above does not test for data present in L. Is this a weakness? I am assuming that L and M will either both be empty or will both contain mail data ? Can your data entry discipline guarantee it?

Hope that helps.