Google-sheets – Google Spreadsheet Advanced Conditional Filter

google sheetsworksheet-function

Let's say I have a money "Transactions" Table with columns: [Timestamp, Account, Category, Inflow, Note, Group].

I would like to use FILTER() function to display it in another page with 5 selectable filters from the following dropdowns: Account, Group, Category, TimestampMonthLowerLimit, TimestampMonthUpperLimit and that's okay until here with

=FILTER(Transactions; Transactions.Account=AccountDropDownCell; Transaction.Category=CategoryDropDownCell; ...)

The fact is that I need an extra option for EACH dropdown (even a side checkbox would work) which takes for example all accounts, or all categories, etc… And the chance to combine those filters in modular way just like the spreadsheet native filter for tables if i choose to not filter at all. Something like: Ok, apply this filter, or do not and show them all.

I hope it was clear enough!

How would you do that?

Best Answer

See testFile

I added support column "Month" in Sheet "DB" in order to filter by timestamp month limits.

Then there is sheet "Lists" with unique values for dropdowns.

On Sheet "multiFilter" there are respective filters and following formula in cell "A4" that returnes values based on selected filters.

=
QUERY(
{DB!A:G},
"Select Col1, Col2, Col3, Col4, Col5, Col6
 Where 
 "&IF(A2="","","Col2 ='"&A2&"' and")&"
 "&IF(B2="","","Col3 ='"&B2&"' and")&"
 "&IF(C2="","","Col5 ='"&C2&"' and")&"
 "&IF(D2="","","Col6 ='"&D2&"' and")&"
 Col7 >= date"&TEXT(E2,"'YYYY-MM-DD'")&" and 
 Col7 <= date"&TEXT(F2,"'YYYY-MM-DD'")&"",1)

If no values selected in filter range - no filter would be applied.

Related Topic