CODEWITHSUNDEEP
google-sheets
bateman_ap
bateman_ap

Reputation: 1921

IF statement in Google Sheet QUERY

I am creating a Google Sheet that sucks in values from Analytics and allows a user to go in, select various landing pages from a dropdown list, and source of traffic and see various stats relating to that source/landing page.

It's all working apart from I want to have a 'all' selection in the sources dropdown.

A simplified version of the formula I am using to generate the results is:

=IFERROR(QUERY('Sheet1'!$A$16:$M$6969,"select sum(E) where (A) contains '" & $B$4 & "' AND B = " & MONTH(B$6) & " AND C = " & YEAR(B$6) & " AND D = '" & $B$3 & "' label sum(E) ''",1),0)

A is the landing page code, i.e. landing-page

B and C just get a date from a header row

D is the one I am struggling with. At the moment B3 is a dropdown containing cpc, organic, direct, all. If I select anything but all it works, but I need to write some sort of condition so if all is selected then AND D = '" & $B$3 & "' isn't used.

I don't seem to be able to put an IF statement within a QUERY so unsure how to proceed!

Upvotes: 0

Views: 11650

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

If you try to generate the Query dynamically, especially with multiple filter conditions it is usually easier to replace the filter condition with 1=1.
This eliminates possible problems of trailing AND and OR clauses.

=IFERROR(QUERY('Sheet1'!$A$16:$M$6969,
               "select sum(E) 
                where (A) contains '" & $B$4 & "' 
                      AND B = " & MONTH(B$6) & " 
                      AND C = " & YEAR(B$6) & " 
                      AND " & IF($B$3="all", "1=1", "D = '" & $B$3 &"'")& "      
                label sum(E) ''",1),0)

Upvotes: 3

Related Questions