This isn't a syntax overview. It's about where QUERY actually earns its place in a financial model, where it breaks, and when to reach for something else.
What QUERY Does That SUMIFS Can't
SUMIFS aggregates. QUERY returns rows. That's the core difference, and it determines which tool belongs where.
When you're building a contribution margin view by product line for a board pack, SUMIFS forces you to write one formula per metric per row. QUERY lets you return the whole slice in one shot — all rows where product_line = "Enterprise", sorted by revenue descending, with a label row at the top. The result spills automatically, which means it updates when the source data does.
Google's documentation describes QUERY as executing "a query on the data using the Google Visualization API Query Language." That language supports SELECT, WHERE, GROUP BY, ORDER BY, LIMIT, and LABEL — roughly 80% of what you'd reach for in a reporting query.
Cross-Tab Syntax for Multi-Tab Models
The part most tutorials skip: referencing another tab. The syntax requires wrapping the range in the function call itself:
=QUERY('P&L'!A:H, "SELECT A, B, C, SUM(F) WHERE B = 'Enterprise' GROUP BY A, B, C LABEL SUM(F) 'Revenue'", 1)
That third argument (1) tells QUERY how many header rows the source has. Miss it and your results include the header as a data row, which breaks aggregations.
For a model where Assumptions drive everything, a dynamic date filter looks like this:
=QUERY('P&L'!A:H, "SELECT A, B, SUM(F) WHERE A >= date '"&TEXT(Assumptions!$B$3,"yyyy-mm-dd")&"' GROUP BY A, B", 1)
The date format matters exactly here. QUERY's date literals need yyyy-mm-dd wrapped in single quotes inside the SQL string. A wrong format returns a parse error that looks like a data problem until you debug it.
WHERE Clauses for Dynamic Filtering
The WHERE clause supports AND, OR, and NOT. For a variance analysis where you want actuals vs. budget for Q1 FY2026 filtered to a specific cost center:
=QUERY('GL Detail'!A:J, "SELECT B, D, SUM(G), SUM(H) WHERE C = 'CC-412' AND A >= date '2026-01-01' AND A <= date '2026-03-31' GROUP BY B, D ORDER BY SUM(G) DESC", 1)
This returns department, account, actual, and budget for one cost center across Q1 — sorted by actual spend so the biggest variances surface first. The equivalent in SUMIFS would be 4 formulas per row, replicated across every account code you care about.
One non-obvious constraint: QUERY can't reference named ranges inside the SQL string. The column reference has to be a letter (A, B, C) or Col1, Col2 notation. Named ranges work as the data source argument, but not inside the query string itself. This catches people when they try WHERE account = SomeNamedRange — it fails silently and returns everything.
Aggregations and GROUP BY
QUERY's GROUP BY replaces pivot tables for programmatic output. The rule: every column in SELECT that isn't an aggregate function must appear in GROUP BY. Violate this and you get a "Column X cannot both be aggregated and not aggregated" error.
Supported aggregate functions: SUM, COUNT, AVG, MAX, MIN. No MEDIAN, no STDEV, no percentile functions — that's the ceiling. For anything beyond these five, you'll need to either post-process QUERY output with additional formulas or use ARRAYFORMULA with more granular functions.
As of April 2026, Google hasn't extended QUERY's aggregate function set since 2018. If median EBITDA by quarter is what you need, use QUERY to pull and sort the rows, then apply MEDIAN to the resulting range with a helper formula.
LABEL, FORMAT, and Output Control
Two clauses that rarely show up in tutorials but matter for board-ready output:
LABEL renames column headers in the result. Without it, aggregated columns inherit names like SUM Revenue or avg(E), which is fine for internal work and embarrassing in a PDF export.
=QUERY('Revenue'!A:F, "SELECT A, B, SUM(C) GROUP BY A, B LABEL A 'Period', B 'Segment', SUM(C) 'Net Revenue ($000s)'", 1)
FORMAT applies a display format to the output, using the same format strings as TEXT(). For currency columns in a board pack:
"... LABEL SUM(C) 'Net Revenue' FORMAT SUM(C) '$#,##0'"
This keeps the underlying number numeric (formulas elsewhere in the model can still reference it) while displaying cleanly.
Where QUERY Breaks in Practice
Three failure modes worth knowing before they bite you in a live model:
Mixed types in a column. QUERY evaluates the column type from the majority of values. If column B is 95% text but has 5 numeric cells, QUERY treats it as text and silently skips the numbers in WHERE comparisons. Clean your source data or add a helper column that forces consistent types.
Blank cells in date columns. A blank cell in a date column makes QUERY return an error when you filter by date range. The workaround is to either filter out blanks with WHERE A IS NOT NULL AND A >= date '...' or ensure the source range has no trailing blanks.
Dynamic sheet references. You can't dynamically reference a sheet name inside QUERY the way you can with INDIRECT. =QUERY(INDIRECT("'"&A1&"'!A:H"), ...) works for the range, but the resulting query string still needs hardcoded column letters. For models where the sheet structure changes, this means QUERY becomes fragile in ways SUMIFS isn't.
QUERY vs. SUMIFS: A Decision Rule
Use QUERY when you need rows back — filtered lists, dynamic report slices, sorted output. Use SUMIFS when you need a single aggregate value in a fixed cell that other formulas reference. The mistake is using SUMIFS to build what amounts to a filtered table, which requires replicating the formula down every row and across every column manually.
A three-statement model with a dedicated Reporting tab pulling from GL Detail is a natural fit for QUERY. The Reporting tab stays current automatically, LABEL handles column naming, and ORDER BY surfaces the data the CFO actually wants to see without manual sorting.
If you're already running AI automation in your Google Sheets workflow, QUERY pairs well: the AI can generate or modify the SQL string while leaving your formula structure intact.