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? 