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!
>Solution :
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)))
