Using SEARCH function to count occurrence of multiple values

0
votes

So I've Two lists in Google sheets. one is a (relatively short) list of names, let's say a rooster of employees. The second list is (rather a long) list of shifts, which notes the employees who were present.

for example:

List A - (rooster):         
___________________

Mike

Linda

Carrie

Dave

List B - (Import_shift_data):
____________________________

Mike, John

Dave, Linda, Mike

Carrie

Dave, John

Linda

Mike

Dave, Carrie, John, Mike

My goal is to count the presence of each employee.

Now, here are the tricky parts:

  1. List B updates every day, and each cell contains more than one name.

  2. List A also updates, as some employees join the team and other leave.

  3. Each shift could by a day shift, or a night shift (listed in another column next to List B) and I need to count them separately.

The Day/night column is in a parallel column next to shift column, and has one of two values, "Day" or "Night"

So my notion was to create an array formula, who can expand or shrink based on the number of values in List A. The problems is, I Can't yield and results from using the whole {list A} as the first argument in the SEARCH function.

I've tried the foloowing:

=Arrayformula(IF(INDIRECT("A2"):INDIRECT(CONCATENATE("A",MAX(Arrayformula(IF(isblank($A:$A),"",Row($A:$A)))))) = 0,"",COUNTIFs('Import_shift_data'!$P:$P,INDIRECT("A2"):INDIRECT(CONCATENATE("A",MAX(Arrayformula(IF(isblank($A:$A),"",Row($A:$A)))))),'Import_shift_data'!$M:$M,"Night")))

. But this formula only works for a shift with a single employee.

I also wrote this one:

=Countifs(Arrayformula(ISNUMBER(SEARCH(A2,'Import_shift_data'!$P:$P))),"true",'Import_shift_data'!$M:$M,"Night")

which works fine, but I need to manually drag it up or down every time List A (The rooster) is updated.

So my end game is to have two arrays, one that counts night shifts for each employee, and one who counts day shifts. those arrays should automatically shrink or expand by the size of the rooster. (List A)

Note: If relevant, I may also note that the names in {List A} may contain more than one word, in case there are two employees with the same first name.

A copy of the spreadsheet:

https://drive.google.com/open?id=1HRDAy9-T_rflFpzanZq0fmHpV0jTZg6Rc4vHyOu-1HI

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querytextjoin
Can you share a copy of your spreadsheet so that we have some data to work with? – JPV
drive.google.com/… – Doron Assis

2 Answers

0
votes

day shift:

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(TEXTJOIN(", ", 1, B2:B), ","))), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''", 0))

night shift:

=ARRAYFORMULA(QUERY(TRIM(TRANSPOSE(SPLIT(TEXTJOIN(", ", 1, C2:C), ","))), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''", 0))

enter image description here

0
votes

I Think I've found the Solution, I've used player0's idea of rearranging the data vector and split non-single shifts into single cells.

so basically it goes:

=Arrayformula(CountiF(Transpose(SPlit(Textjoin(" , ",TRUE,QUERY('Import_shift_data'!A:P, "select P where M = 'Night' ", 1))," , ",False)),INDIRECT("A2"):INDIRECT(CONCATENATE("A",MAX(Arrayformula(IF(isblank($A:$A),"",Row($A:$A))))))))

Thanks player0 !