I would like to count the total occurrences of series that have “Action” as a main genre and then “Magic” as one of it’s other tags. Currently I have:
=Query(A:BM,"Select Count(L) where (L='Action')")
L being the row where the main genre is listed. Following that row (M:BM) are a bunch of tags. Is it possible to use query to tally that up? Something like:
=Query(A:BM,"Select Count(L) where (L='Action') and (M:BM='Magic')")
Which doesn’t work, but if there’s another way.
I tried the COUNTIFS but since the range is different, it also doesn’t work:
=COUNTIFS ('SheetName'!$L:$L, "Action", 'SheetName'!$M:$BM, "Magic")
Perhaps a combo of Filter and COUNTIF? Combining Filter (for Action), and then countif across a range…
sample of what the sheet looks like
On a separate sheet (in the same book), counting “Action” series w/ “Magic” tag would ideally turn up something like this, if there were two series tagged with “Action” as a main genre, and then “Magic” somewhere in one of the tag columns:
Link to sample sheet
Thank you!!
Advertisement
Answer
It is always easier by far to develop solutions when one has access to the actual data. However, “eyeing it,” I’ve created a formula you can try:
=ArrayFormula(COUNTA(IFERROR(REGEXEXTRACT(TRANSPOSE(QUERY(TRANSPOSE("~"&L2:BM&"~")," ",COLUMNS(L2:BM))),"~Action~.+~Magic~"))))
Let’s make sure it works as expected before I invest the time into explanations.
If this doesn’t work as expected, please share a link to a copy of the sheet, being sure to set the permission (when creating the link) to “Anyone with the link…” and “Editor.”