Checking multiple cells to see if they contain any of a list of choices

[Atlas87]

Good morning guys...

I have been racking my (less-than-ample) brain on this one for a couple of days... please help...

If i have a questionnaire which contains something like "What countries do you do business with?" and it yields a single cell with all their answers delimited by a comma, is there a way i can check to see which/how many of those answers are on another list i have? I know that i can "text to columns" to split them but i don.t know how look through that list against another set of multiple criteria.

Essentially, a horizontal or vertical group of cells containing countries which i want to check against another high risk country list.

To illustrate....

Their answer list; UK, USA, Nigeria, Iraq, France Germany

My high risk country list; Nigeria Iraq, UAE, iran, Syria

Question; Is there a single formula to check how many of my high risk countries appear in the respondents answer list?

Huge thanks in advance!!!

 

[steve the fish]

With the answer list in A1 and your high risk countries in B1:B5:

=SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$5,A1)))

 

[AlanY]

with your answer cell in A1

=SUMPRODUCT(--(ISNUMBER(SEARCH({"Nigeria","Iraq","UAE","Iran","Syria"},A1))))

 

[Atlas87]

This is outrageous - Steve i could kiss you...but i wont, and not because you are a fish.

Two questions though....

1. It yielded an answer of 1 where i know from manual checking it should have said 2. Any thoughts?

2. For my own interest/learning....What is the significance of the --

 

[Atlas87]

This is outrageous - Steve i could kiss you...but i wont, and not because you are a fish.

Two questions though....

1. It yielded an answer of 1 where i know from manual checking it should have said 2. Any thoughts?

2. For my own interest/learning....What is the significance of the --

EDIT: I need to put the comas in per the exact nature of the list for the right answer....

 

[steve the fish]

If we split the formula out it starts by checking the position of the contents of B1 within A1. Then does the same for B2 within A1 etc. This creates an array eg:

{10;20;#value !;#value !;#value !}

isnumber then converts this to:

{true;true;false;false;false}

the double negative -- will mean excel will try to convert the text to a number. In this case it can so it becomes:

{1;1;0;0;0}

Finally the sumproduct adds them:

2

 

[Atlas87]

If we split the formula out it starts by checking the position of the contents of B1 within A1. Then does the same for B2 within A1 etc. This creates an array eg:

{10;20;#value !;#value !;#value !}

isnumber then converts this to:

{true;true;false;false;false}

the double negative -- will mean excel will try to convert the text to a number. In this case it can so it becomes:

{1;1;0;0;0}

Finally the sumproduct adds them:

2

OK - slight problem with this again...maybe i'm doing something wrong....i'm getting bizzarely high (and wrong) answers...

for cells with only 5 entries, the formula is returning 13.

for some cells with three entries it is returning 23

theres even a cell with one country in it which has returned 27.

 

[steve the fish]

You are doing something wrong. Its not possible for the formula i provided to produce any number higher than that the number of cells in the SEARCH find_text. In the case of the provided formula its a max of 5 (B1:B5 being 5 cells). Id imagine you have done something with the ISNUMBER part.