Search Contents of Multiple Rows For Specific Text Using Array Formula in Google Sheets

Question

I have a column where each row contains multiple words, example below:

Fight; (People: Jack, Gil, Row, Pitt); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow,)
Fight; (People: Jack, Clive, Gareth); (Link: Box, Wave, Arrow)
Fight; (People: Jack, Gil, Jan); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow, Square, Attack)

I'm trying to pull specific words from those rows so that I can tally other information, such as how many times those words appear or how much time are associated with those words. The issue is I haven't found a way to specifically search for that word and that word only.

Column A - Type	Column B - Time
Fight; (People: Jack, Gil, Row, Pitt); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow)	0:02:43
Fight; (People: Jack, Clive, Gareth); (Link: Box, Wave, Arrow)	0:01:54
Fight; (People: Jack, Gil, Jan); (Link: Box, Wave, Line, Guard, Circle, Wide, Arrow, Square, Attack)	0:00:23

When searching the above information with the following formula, I get these results:

**Participation #** 
=ARRAY_CONSTRAIN(ARRAYFORMULA(FILTER(COUNTIF($A2:$A4, "*" &$D8:$D14 & "*"),NOT(ISBLANK($D8:$D14)))),COUNTA($D8:$D14),1)

**Time** 
=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(SUMIF($A2:$A4,"*" & $D8:$D14 & "*",$B2:$B4),"")),COUNTA($D8:$D14),1)

People	Participation #	Time
Jack	3	0:05:00
Gil	2	0:03:06
Row	3	0:05:00
Pitt	1	0:02:43
Clive	1	0:01:54
Gareth	1	0:01:54
Jan	1	0:00:23

Row is being counted 3 times, despite only participating once, because of the word "Arrow". Is there a modification I can make so that I only search for the specific word? Google Sheet for reference and thank you for your time!

Answer 1

Try in E8:

=ARRAYFORMULA(IFNA(VLOOKUP(D8:D14, QUERY(SPLIT(FLATTEN(SPLIT(A2:A4, ":;, ()", 1)&"×"&B2:B4), "×"), 
 "select Col1,count(Col1),sum(Col2) where Col2 is not null group by Col1"), {2, 3}, 0)))