| Movies | Genre(s) of the movie | All Genres | Number of movies for the genre |
|---|---|---|---|
| Spider Man | action, comedy | action | =countif(B1:B, C1) results 0. it should result 2 |
| James Bond | action, crime, thriller | comedy | =countif(B1:B, D2) results 1. it should result 2 |
| Grown Ups | comedy | crime | =countif(B1:B, D3) results 0. it should result 1 |
| Halloween Kills | Horror | thriller | =countif(B1:B, D4) results 0. it should result 1 |
| Dune | sci-fi | horror | =countif(B1:B, D5) results 1. it should result 1 |
| sci-fi | =countif(B1:B, D6) results 1. it should result 1 |
So I have something like this in google sheets, is there a way to count the number of movies for each genre. I tried doing what i have in column 4, but only got the number of movies where the genre was the only genre.
Is it possible to do what I want with an if formula in google sheets? For instance, if(there is a match doing regex in this column); count = count 1.
CodePudding user response：
try:
=INDEX(QUERY(TRIM(FLATTEN(SPLIT(TEXTJOIN(",", 1, LOWER(B2:B)), ","))),
"select Col1,count(Col1) group by Col1 label count(Col1)''"))
if you got "special" order use:
=INDEX(IFNA(VLOOKUP(D2:D, QUERY(TRIM(FLATTEN(SPLIT(TEXTJOIN(",", 1, LOWER(B2:B)), ","))),
"select Col1,count(Col1) group by Col1 label count(Col1)''"), 2, 0)))
CodePudding user response：
I think I got the below wrong because you are trying to count all movies.
use this
=COUNTIF(B:B, "*"&"action"&"*")
Use this expression (defunct as I thought you were searching horizontally)
=COUNTIF(B1:C1, "*"&C1&"*")
C1 can be replaced by a word:
=COUNTIF(B1:C1, "*"&"action"&"*")