Count occurrences of sequential range values in another range

dityo

New Member
Joined
Jan 7, 2017
Messages
3
[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?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,631
This works for the posted example:

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

dityo

New Member
Joined
Jan 7, 2017
Messages
3
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,183
Office Version
  1. 2016
Platform
  1. Windows
Is there a maximum number of names that can appear in column B ?
 

dityo

New Member
Joined
Jan 7, 2017
Messages
3
Yes, which is the no of names in column A, which in my case is 400 :(
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top