Count occurrences of sequential range values in another range

[dityo]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]Hi there,[/FONT]

My example is that on column A, I have different names.
Let these be, in order of appearance, starting with <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">A1</code>:
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">{Joe, Michael, Ana, Victor, Joe, Ana, George, Victor, Chris, George, Joe, Michael, Ana, Victor, Joe, Michael}</code>
So Joe is in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">A1</code>, Michael is in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">A2</code>, Ana in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">A3</code> etc.
On column B I have the values of subset which I want to search for in the set of values of names from column A. Let this subset, starting with B1, be:
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">{Joe, Michael}</code>
So Joe is in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">B1</code> and Michael is in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">B2</code>.
I am looking for a single formula:

• Which will not use calculations from other cells
• Which will count how many times will the values of the subset be included, sequentially, in the order provided (e.g. Joe, Michael), in column A.

For the this example it should return value 3.
This being said , I actually need a more general formula which will work for any subset, independently of the number of elements of it.
Please note that there is no information about the maximum size of the subset.
Can anyone suggest a formula?

 

[Tetra201]

This works for the posted example:

=SUMPRODUCT(--(A1:A15&A2:A16=B1&B2))

 

[dityo]

Thank you for your answer.
It does, but only for the particular example in which the number of names in the B column is fixed.
However, this number may vary, and I need a formula which will have this flexibility built in.

 

[footoo]

Is there a maximum number of names that can appear in column B ?

 

[dityo]

Yes, which is the no of names in column A, which in my case is 400