Search code examples
google-sheetsgoogle-sheets-formulaformula

How can I filter B9:NC49 based on criteria set in column B (B56:B62) and if its adjacent column (C) (C56:C62) is checked in Google Sheets?


I am trying to get this one to filter, but it just shows everything:

=IF(COUNTIF('Cash Flow'!C56:C62,TRUE)>=1, 
    FILTER('Cash Flow'!B9:NC46, REGEXMATCH('Cash Flow'!B9:B46,JOIN("|",IF('Cash Flow'!C56:C62=TRUE,'Cash Flow'!B56:B62,"")))), 
QUERY('Cash Flow'!B9:NC46,"select * where B is not null and not B matches '%' and not B matches 'Pending'",1))

What might be the reason why?


Solution

  • Well goin' in blindly; can you give a shot at:

    =IF(COUNTIF('Cash Flow'!C56:C62,TRUE)>=1, 
        FILTER('Cash Flow'!B9:NC46, REGEXMATCH('Cash Flow'!B9:B46,TEXTJOIN("|",1,IF('Cash Flow'!C56:C62=TRUE,'Cash Flow'!B56:B62,"")))), 
    QUERY('Cash Flow'!B9:NC46,"select * where B is not null and not B matches '%' and not B matches 'Pending'",1))