Count occurrences of sequential range values in another range

Question

My example is that on column A, I have different names.

Let these be, in order of appearance, starting with A1:

{Joe, Michael, Ana, Victor, Joe, Ana, George, Victor, Chris, George, Joe, Michael, Ana, Victor, Joe, Michael}

So Joe is in A1, Michael is in A2, Ana in A3 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:

{Joe, Michael}

So Joe is in B1 and Michael is in B2.

I am looking for a single formula that:

• Which will not use calculations from other cells
• Which will count how many times will the values of the subset be included, 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?

Answer 1

Here is an array formula that may be of assistance to you. It is using Column A offsetted :

=SUM(IF(EXACT(A1:A499&A2:A500,B$1&B$2),1,0)) and then CTRL+SHIFT+ENTER

Answer 2

The following formula will improve incrementally upon the previous answer to handle up to four names in the susbet. I limited it to four in order to avoid a "Sunday Newspaper" length of a formula.

IF(COUNTA(B1:B300)=2,SUM(IF(EXACT(A1:A499&A2:A500,B$1&B$2),1,0)),IF(COUNTA(B1:B300)=3,SUM(IF(EXACT(A1:A499&A2:A500&A3:A501,B1&B2&B3),1,0)),IF(COUNTA(B1:B300)=4,SUM(IF(EXACT(A1:A499&A2:A500&A3:A501&A4:A502,B1&B2&B3&B4),1,"")),""))).

Of course, you can personally expand it further to cover five and upwards subsets of names by nothching up the "A" range and then the "B" range.

Answer 3

A different approach to the problem will help. Copy all the names in Column A to an unused Column. Then go to the Data tab and use "Remove Duplicates". Sort these unique values in alphabetical order, copy them, and then transpose ( paste) them into an unused Row.

Put this formula in the entire row, in each cell immediately under the unique names : =RANDBETWEEN(1,1000000). With both rows selected, go to the "Formula" tab, then "Defined Names" and select "Create From Selection". Click the "Top Row" box.

Column A holds your thousands of names as you stated. Type this formula in B1 and fill down : =INDIRECT(A1).

In C3 type : =IF(AND(EXACT(CONCATENATE(OFFSET(B1,0,0,COUNT(E$1:E$2000))),CONCATENATE(OFFSET(E$1,0,0,COUNT(E$1:E$2000),1)))),1,""). Column D holds the Subset of name values.Type this formula in E1 and copy down as far as D goes ( the subset ) : =IFERROR(INDIRECT(D1),""). Finally, use a SUM function placed wherever you like to get a count of the subset values in Column A : =SUM( C:C).