Counting the occurrence of specific words over multiple tabs in Google Sheets

Question

I want to count how many times a specific word occurrs in column B across mulitple tabs/sheets in Google Sheets.

I want to have the solution as flexible as possible, as I will be adding tabs/sheets in the future. All my sheets are named as S1, S2, ..., up to S10 currently.

I want to have a front sheet with all the statistics and let the raw data be in their respective tab/sheet. The word I want to search for is in cell B2 and in cell C2 I want to have the count. I will then do the same thing again for a new word in cell B3 and so on.

I have browsed this site for like 2 hours by this point, trying to find the best solution.

I have tried using QUERY, but as I've adjusted QUERY expressions I've found in comments to suit my spreadsheet they never work.

I have also tried using sumproduct-countif-indirect as it's been suggested in numerous comments, like this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!B:B"), B2))

Where SheetList is a Named Range (basically an array with the sheet names), but that won't work either.

I have also tried it with =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A11&"'!B:B"), B2)) where A2:A11 is a list of all the sheet names.

The problem is that the function only returns the value 1 when it should return the value 9.

Here is a simplified copy of my data and the function I'm using.

I really wanted to find an elegant solution to this (seriously, why can't Google just add a count-function that does this simple thing??) but it has now taken me several hours.

I'm really thankful for any help I can get!

Answer 1

You can use REDUCE to sum the amount of occurences, with that you can use the range of sheet names to iterate over the process:

=REDUCE(0;TOCOL(A2:A;1);LAMBDA(a;sheet;
a+COUNTIF(INDIRECT(sheet&"!B:B");B2)))

If you want to do it as an array formula:

=BYROW(TOCOL(B2:B;1);LAMBDA(name;
REDUCE(0;TOCOL(A2:A;1);LAMBDA(a;sheet;
a+COUNTIF(INDIRECT(sheet&"!B:B");name)))))