CODEWITHSUNDEEP
google-sheets
Tommy
Tommy

Reputation: 25

Filter a query with multiple conditions

I'm trying to create a table that filters a recorded financial statement by month, as well as by 'tags'. Each line item in the statement will have these tags attributed to them, so that I can filter them later. I'm currently having trouble figuring out a formula that allows me to filter a query based on multiple conditions that are chosen via several different dropdown lists.

So for example, I would like to filter A6:G41 by month, which I've queried as such:

=query(A6:G41," select * where A >= date '"&TEXT(O3,"yyyy-mm-dd")&"' and A <= date '"&TEXT(P3,"yyyy-mm-dd")&"' " )

A6:G41 is the table I'm querying, while O3 and P3 are are the beginning, and end dates of the months, giving me all of the transactions for a specified month. I would like to add further conditions to filter such as 'meals, meetings, computer equipment', that will be designated in cells R1,R2, and R3--which are dropdown lists.

Any thoughts?

Here is a link to a sample sheet I made for reference: https://docs.google.com/spreadsheets/d/1dKnUeEad5LyX73Eo6kF-RXFakrhGSty67iKmhPoUbEc/edit?usp=sharing

Upvotes: 1

Views: 1244

Answers (1)

user6655984
user6655984

Reputation:

If all your query does is filtering (no aggregation, comparison of columns, pivot, etc), then it's better to use filter instead. One advantage: substantially less fuss about data types (such as conversion of dates to text and then back to dates, as in your example). The query you've created so far is equivalent to 

=filter(A6:G41, A6:A41 >= O3, A6:A41 <= P3)

I would drop "41" from here (since the list may grow) and continue adding conditions:

=filter(A6:G, A6:A >= O3, A6:A <= P3, I6:I = R1)

also requires column I to be equal to R1. If a looser match is required, use regexmatch with a regular expression: 

=filter(A6:G, A6:A >= O3, A6:A <= P3, regexmatch(I6:I, "(?i)travel"))

matches "travel" anywhere in I, case-insensitive.

Conditions can also be joined by "OR" logic, by placing + between them, like this: 

=filter(A6:G, A6:A >= O3, A6:A <= P3, (I6:I = R1) + isblank(R1))

Here the third condition holds if either I column is equal to R1, or R1 is blank.

Upvotes: 2

Related Questions