Google Sheets: iterate through a list of keywords in a filter formula-CodePudding

I have a list of keywords (each in separate cell), for example:

and I have a formula that searches for these keywords across different worksheets and returns the sum of values corresponding to a given keyword:

=SUM(FILTER(INDIRECT("'"& B6 & "'!G1:G600");INDIRECT("'" & B6 & "'!B1:B600")=A6))

where A6 contains an in-cell dropdown list of these keywords to choose from and B6 contains an in-cell dropdown list of worksheets to work in (let's say for different months).

Example: When 'Keyword A' is found in worksheet 'January' in column B, find the corresponding value to this keyword in column G; then ultimately sum all of these values for a given worksheet and a given keyword.

Depending on what I choose from the in-cell dropdown list, I get the corresponding sum for a given keyword. However, I'd like to get the total sum of all the keyword values added together, but I can`t seem to make it work. Feels like I'd have to use some equivalent of a for loop to iterate through the list and add up the individual sums, but as far as I'm concerned, there's no looping possible in Google Sheets.

I can, obviously, define these sums for each keyword separately and then add these values together, but as I'm working with large datasets with many keywords, this solution is rather unpleasant.

All suggestions are much appreciated!

CodePudding user response：

Building on player0's answer, this formula, though a bit simplified, seems to be working just fine (taking a list and looking for members in a given spreadsheet to include in the sum), using Reduce and Query:

    =REDUCE(0; QUERY(INDIRECT("'"& B6 & "'!B7:G600");
     "select C where B matches '"&TEXTJOIN("|"; 1; L4:L)&"'"); 
     LAMBDA(x;total;total x))

CodePudding user response：

try REDUCE:

=INDEX(QUERY(REDUCE({""\""}; H4:INDEX(H:H; MAX(ROW(H:H)*(H:H<>""))); LAMBDA(a; b; 
 {a; IFERROR(INDIRECT(b&"!B4:C"); {""\""})})); 
 "select sum(Col2) where Col1 matches '"&TEXTJOIN("|"; 1; I4:I)&"'"); 2)

see: