Google Sheets QUERY turns any range into a tiny SQL-style database, and the WHERE clause is one of the workhorses you will use almost every time. This tutorial covers what WHERE does, the syntax peculiarities (column letters instead of names, single quotes for strings, curly-brace arrays), and three worked examples that mirror real-world reporting needs.
Reading QUERY syntax
A QUERY call looks like =QUERY(data, query_string, headers). The data range can be a literal range (A1:F), an IMPORTRANGE result, or even a stacked array with curly braces. The query_string uses Google Visualization API SQL — similar to standard SQL but with a few quirks: columns are referenced by letter (A, B, …) when the source is a range, and string literals use single quotes.
WHERE in detail
Filter rows by one or more conditions.
Worked example
=QUERY(A:F, "SELECT * WHERE B = 'Active'", 1)Reading left to right: pick columns A and SUM of F, only from rows where channel B equals 'Web', group the surviving rows by A, sort by total descending, keep the top ten. The trailing 1 tells QUERY there is one header row in the source. If you forget it, QUERY guesses and frequently guesses wrong on mixed-type columns.
Combining clauses
The clauses execute in a fixed order regardless of how you write them: SELECT → WHERE → GROUP BY → PIVOT → ORDER BY → LIMIT → OFFSET → LABEL → FORMAT → OPTIONS. That order matters when you reason about what each clause sees. WHERE filters raw rows; GROUP BY collapses survivors; ORDER BY sorts the collapsed result; LIMIT trims the sorted result. Keep that pipeline in mind and most QUERY confusion evaporates.
Worked examples
Top 10 customers by revenue
=QUERY(Sales!A:F, "SELECT A, SUM(F) GROUP BY A ORDER BY SUM(F) DESC LIMIT 10 LABEL SUM(F) 'Revenue'", 1)LABEL renames the aggregated column so the header reads 'Revenue' instead of 'sum F'.
Filter by date range
=QUERY(Sales!A:F, "SELECT * WHERE C >= date '2025-01-01' AND C < date '2025-02-01'", 1)Dates need the date 'YYYY-MM-DD' literal in QUERY — a bare date does not work.
Aggregate across two sheets
=QUERY({Q1!A:F; Q2!A:F}, "SELECT Col1, SUM(Col6) GROUP BY Col1", 1)Stacked arrays use Col1, Col2 references because there is no original column letter.
Common mistakes (and how to avoid them)
- Forgetting the headers argument. QUERY guesses, and you get a header row mixed into the data when it guesses wrong.
- Double quotes inside the query string. QUERY is itself wrapped in double quotes — use single quotes for string literals.
- Comparing dates without the
date '...'constructor. Sheets evaluates a bare date in the wrong type and silently returns nothing. - Column letters change when you use a stacked array. Switch to
Col1,Col2, … inside the query string.
Tips that pay off later
- When a QUERY returns nothing and you cannot tell why, paste the WHERE clause one condition at a time to isolate the culprit.
- Wrap QUERY in IFERROR for a friendly empty-state message:
=IFERROR(QUERY(...), "No matching rows"). - Combine with IMPORTRANGE for cross-file aggregation:
=QUERY({IMPORTRANGE(...)}, "SELECT Col1, SUM(Col2) ...").
When to reach for something else
If you only need a simple sum-by-condition, SUMIFS is faster and simpler. QUERY shines when you need multiple aggregates, sorting, or pivoting in one step.