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]

PMC11,

Does this help the cause any ?

=IF(COUNTA(D2:E2),"Do not invite",IF(AND(COUNTA(F2),NOT(COUNTA(L2:M2))),"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(COUNTA(M2),"Mail - International",IF(COUNTA(K2:M2),"","No Contact Info"))))))))

Not an array formula.

 

[PMC11]

Hi, thanks for your excellent response. You have solved the issue of having a long list of countries.

For instances when there is "no email" in column F, your formula produces the correct categorisation where there is no email address in the record (when column K is blank). However when there is "no email" in column F and an email address in column K the formula returns "Evite" which isn't quite what I was after. Basically we can still contact these people but only by mail, even if we do have an email on file for them, if they have a "no email" solicit code. Perhaps there's a small modification you could recommend?

Thanks a lot for your help so far, it is most appreciated. Cracking this could save so much time.

 

[Snakehips]

How about....

=IF(COUNTA(D2:E2),"Do not invite",IF(AND(COUNTA(F2),NOT(COUNTA(K2))),"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(COUNTA(K2:M2),"","No Contact Info"))))))))

 

[PMC11]

Hi, thanks again for your help. This formula work perfectly for instances where there is an email address (column K) present and the No E-Mail code in column F.

The problem now however is when there is no email address in column K, and the No E-Mail message in column F. The formula returns "Do Not Invite" when it should return the Mail based on country. Also strangely it returns a blank when there is an email address and the no e-mail code but no address.

Do you think this problem is possible to solve in a formula or would I need to use VBA (something of which I have no knowledge)? It would be best to keep it as a formula for sake of ease in the future.

Thanks again for all of your help so far.

 

[Snakehips]

I will take one more look at it.
The second last element of your original formula returns a blank. Is that not ever a valid result?

 

[PMC11]

Thinking about it, no. All records should have something returned by the formula. I think I included the blank because I didn't think it would be possible for it to be ever returned. You may be able to tell this has really pushed my excel knowledge. Would it be easier if I uploaded a sample of the data somewhere?

 

[Snakehips]

Just try this first and report back .

=IF(COUNTA(D2:E2),"Do not invite",IF(AND(COUNTA(F2),NOT(COUNTA(K2))),"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","No Contact Info")))))))

As I see it the restrictions on contact are basically working left to right with leftmost priority. Therefore your data input must be somehow disciplined ?
Would you ever have no mailing data in L & M yet have an email address and 'No Email' in F ??

 

[Snakehips]

Try this instead...
=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","No Contact Info"))))))

 

[PMC11]

Hi,

That worked! It returned only one error - when there is an email address present, no country, and no e-mail code it returned "no contact" instead of "do not invite". Ultimately this makes no difference to what we do with the data afterwards, and considering it was tested on 2,222 records I'm very happy with the result. Thanks very much for your help.

Are you able to give an exposition on why your formula ended up working? I've gone through and understand each of the steps, but I'm just interested in the logic of your formula versus my original attempt. Yours is much simpler than how I was first approaching the problem and I am interested in learning where I was going wrong.

Thanks again for all of your help. This will save my colleagues and I a huge amount of time.