0

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

Doron Assis
  • 23
  • 4

2 Answers2

0

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • First of all, thank you for your response !. About the answer, the data set is arranged a bit differently (You can see it in link above), basically there are Three columns relevant, one is the rooster, the second is the shift, and the third is Boolean ("Day" / "Night"). I may also forgot to mention that a name may also contain more than one word, (like family name, in case two employees have the same first name). – Doron Assis Aug 15 '19 at 06:15
0

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 !

Doron Assis
  • 23
  • 4