Count Names In Multiple Rows & Group Them By Location In Google Sheets-CodePudding

I've been attempting to use Query to group up and count information in the Attempts column below, but I'm running into walls.

When using a Query formula like below, I seem to be on the right track in getting all the information I want to see, but I'm not sure where to go from here.

=ARRAYFORMULA(QUERY(TRANSPOSE(IFERROR({A3:A6,SPLIT($D3:$D6, ":;, ()", 1)}, "")), "select *"))

My desired result is to essentially count all the people in Column D and associate them with the location. Ideally, if I could get it so that the formula performed in the way of "If A12:A14 Matched A3:A6, it would group all the results together of D3:D6 and then count them, displaying the results in D12:D14". I appreciate your assistance and time! Google Sheet for reference:

CodePudding user response：

try:

=INDEX(IFNA(VLOOKUP(A12:A14, 
 QUERY(FLATTEN(IF(SPLIT(D3:D6, ",")="",,A3:A6)), 
 "select Col1,count(Col1) group by Col1"), 2, )))