Google Sheets - How to wrap OR function for Index, Text not working in formula-CodePudding

At the end of the day, in my Google Sheet I'm trying to filter a large data set by multiple criteria and sum a certain column. I have a mostly working formula but I'm running into a problem.

Here is an example that works:

=sum(INDEX(FILTER('Invoice Data'!H3:P,
'Invoice Data'!N3:N>=DATE(2022,1,1),
('Invoice Data'!H3:H="Arc") ('Invoice Data'!H3:H="Technical Products")
),0,9))

The () () in the second criteria works well as an OR condition. However, I want that criteria to be dynamic based on other information. So I've created the following formula that generates a text string as follows:

="('Invoice Data'!H3:H="&CHAR(34)&join(CHAR(34)&") ('Invoice Data'!H3:H="&CHAR(34),FILTER('Dropdown Menus'!D2:D34, A2='Dropdown Menus'!C2:C34))&CHAR(34)&")"

This successfully generates the text "('Invoice Data'!H3:H="Arc") ('Invoice Data'!H3:H="Technical Products")".

The problem is, when I put it into the original formula, it doesn't work.

=sum(INDEX(FILTER('Invoice Data'!H3:P,
'Invoice Data'!N3:N>=DATE(2022,1,1),
TO_TEXT("('Invoice Data'!H3:H="&CHAR(34)&join(CHAR(34)&") ('Invoice Data'!H3:H="&CHAR(34),FILTER('Dropdown Menus'!D2:D34, A2='Dropdown Menus'!C2:C34))&CHAR(34)&")")
),0,9))

I get the following error:

FILTER has mismatched range sizes. Expected row count: 27436. column count: 1. Actual row count: 1, column count: 1.

Any thoughts on what might be happening? I try to use "Indirect()" to have it be a cell reference, but that didn't work either.

CodePudding user response：

You cannot make a text string become an executable formula that way.

What you can do is use the query() function and build the query statement dynamically.

For easier debugging, put the formula that builds the query statement text string in a cell of its own, and refer to that cell in the query(), like this:

=query('Invoice Data'!H3:P, S2, 0)

...where cell S2 contains your query statement.

CodePudding user response：

instead of your OR try:

=SUM(INDEX(FILTER('Invoice Data'!H3:P,
 'Invoice Data'!N3:N>=DATE(2022,1,1), REGEXMATCH(
 'Invoice Data'!H3:H, "(?i)Arc|Technical Products")),,9))

where

"(?i)Arc|Technical Products"

can be dynamically referred as:

"(?i)"&TEXTJOIN("|", 1, FILTER('Dropdown Menus'!D2:D34, A2='Dropdown Menus'!C2:C34))