CODEWITHSUNDEEP
google-sheets
Umar.H
Umar.H

Reputation: 23099

Multiple IF statements with a drop down Google Sheets Query

Consider the following,

I have three columns,

date, sales rep, revenue,

Date,   Sales Rep,    Revenue,
1/1/18  Jon           £250
1/1/18  Evie          £350
1/1/18  Muhammad      £220

Now, to query this data I can do the following:

IF(a2="","", QUERY(B2:C4, "Select * Where A = '"&A2&"')

now what I want to know is how one would nest multiple queries into one cell if one had multiple drop downs for the above. One selecting sales rep and one for date. So if one Selected a Sales rep & a date, they would get the specific date and sales rep but if one selected JUST a sales rep they would get all their sales regardless of date, and the reverse if possible.

I've tried an OR statement

IF(OR(a2="","", QUERY(B2:C4, "Select * Where A = '"&A2&"'), IF(a3="","", QUERY(B2:C4, "Select * Where b = '"&A3&"'))) but this didn't work.

I've created some dummy data and an open sheet for your convenience.

https://docs.google.com/spreadsheets/d/15nlBPM9u3g0XNE3xbBgSZJxyHz2JjP9io1LfqGgX4CI/edit?usp=sharing

Upvotes: 0

Views: 1561

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

It is easier to work on the SQL clause a piece at a time and then pull it together, I think. So for instance in J2 you could put

=if(ISBLANK(E2),"","B = '"&E2&"'")

which is either empty or a piece of the where clause.

In J3 do

=if(ISBLANK(E3),"","A = Date'"&text(datevalue(E3),"yyyy-mm-dd")&"'")

then J4 gets

=TEXTJOIN(" AND ",true,J2:J3)

and clinch with J5 as follows.

=if(isBlank(J4),"",query(A:C,"SELECT * WHERE " & J4))

Upvotes: 1

Related Questions