QUERY, advanced: conditions, dates, pivot and other tabs

The QUERY clauses you know cover most reports. Four extras cover nearly everything else.

1. Several conditions. Join tests with and, or and not. and binds tighter than or, exactly like multiplication before addition, so use parentheses to say what you mean:

select A, E where (B = 'Paid Search' or B = 'Paid Social') and E > 5000

Without the parentheses, the $5,000 test only applies to Paid Social. Text tests go beyond =: contains 'brand', starts with 'Q3', ends with '.pdf', and matches '.*boots?' for a full regular expression.

2. Dates. A date in a query is written date 'yyyy-mm-dd', always in that order:

where A >= date '2026-03-01' and A < date '2026-04-01'

To drive it from a cell, build the text with TEXT and &:

=QUERY(Log!A1:E500, "select B, sum(E) where A >= date '" & TEXT(H1, "yyyy-mm-dd") & "' group by B")

The quote marks pile up: double quotes end and restart the formula's text, single quotes wrap the date inside the query. Other date tools inside a query: year(A), month(A) (January is 0, not 1) and dayOfWeek(A).

3. Pivot. pivot turns the values of a column into column headers, like the Columns box of a pivot table:

=QUERY(A1:C13, "select B, sum(C) group by B pivot A")

If A holds months and B channels, you get one row per channel and one column per month, each cell the total leads. Pivot columns come out in sorted order, so text months land as Apr, Feb, Jan, Mar. Use real dates or labels like 2026-01 that sort correctly.

4. Other tabs and other files. QUERY reads another tab like any range: =QUERY(Campaigns!A1:E500, "..."), and inside the query the columns are still A to E. When the data comes from a formula, like IMPORTRANGE or FILTER, it has no sheet columns, so name them Col1, Col2 and so on. FILTER results have no header row either, so pass 0 as QUERY's third input:

=QUERY(FILTER(Log!A2:E500, Log!A2:A500 >= H1), "select Col2, sum(Col5) group by Col2", 0)

That FILTER-then-QUERY pattern does the date cut with an ordinary comparison, so you never fight the quote marks. You'll use it in the weekly report capstone.