How to use QUERY with multiple ranges in Google Sheets
By the Analistable team · Updated · 5 min read
Stack the ranges inside QUERY's first argument: =QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C}, "select Col2, sum(Col3) where Col1 is not null group by Col2", 0). Because the data is an array rather than a sheet range, columns must be called Col1, Col2, Col3 — not A, B, C — and every range must have the same number of columns.
Part of our guide: How to combine data in Google Sheets
The pattern
=QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C},
"select Col2, sum(Col3)
where Col1 is not null
group by Col2
order by sum(Col3) desc
label sum(Col3) 'Units'", 0){range; range}stacks ranges vertically (a semicolon means “new rows”). VSTACK works too.Col1is the first column of the stacked array, whatever its letter in the source.where Col1 is not nullremoves the empty rows that open-ended ranges add.- The last argument
0says the data has no header row.
| Product | Units |
|---|---|
| Chair | 96 |
| Desk | 41 |
| Lamp | 27 |
Common errors
| Error | Cause | Fix |
|---|---|---|
| Unable to parse query string… NO_COLUMN: A | Using A, B with an array | Use Col1, Col2… |
| In ARRAY_LITERAL, an Array Literal was missing values | Ranges have different widths | Make every range the same number of columns |
| Numbers missing in sum() | A column mixes text and numbers | QUERY uses the majority type per column; clean the column |
| Formula parse error | Locale separators | Some locales use \ between columns in { } |
Ranges from other files
Replace a tab range with IMPORTRANGE("url", "Tab!A2:C"). QUERY over IMPORTRANGE also uses Col1 notation. See combining multiple IMPORTRANGE formulas.
What QUERY can't do
QUERY works on one table at a time, so it can't join two tables on a key. To combine tables by ID, use lookups as shown in linking two Google Sheets, or stack and group as above when the tables share columns.
To ask questions like this in plain language across Sheets and Excel files, Analistable writes the SQL (which can join) for you.
More useful patterns
Filter by a date range:
=QUERY({Jan!A2:C; Feb!A2:C}, "select * where Col1 >= date '2026-01-15' and Col1 < date '2026-02-15'", 0)
Count rows per tab (add the tab name first with HSTACK):
=QUERY(VSTACK(HSTACK(IF(Jan!A2:A<>"","Jan",), Jan!A2:C), HSTACK(IF(Feb!A2:A<>"","Feb",), Feb!A2:C)),
"select Col1, count(Col2) where Col2 is not null group by Col1", 0)Dates in QUERY need the date 'yyyy-mm-dd' literal. In the second formula, the stacked array starts with the tab name, so the original columns shift one to the right (Col2, Col3…). Depending on your version, the IF inside HSTACK may need ARRAYFORMULA around it.
Step by step: build the formula
- Check that every tab has the same columns in the same order — here Date, Product, Units in A:C.
- On a Summary tab, start with the stack alone:
={Jan!A2:C; Feb!A2:C; Mar!A2:C}. You should see all rows, with gaps where each tab's empty rows are. - Wrap it in QUERY with
select *and the not-null filter to confirm the gaps disappear. - Replace
select *with the columns and aggregation you need. - Add
labelclauses so the output headers are readable.
Building in stages makes it obvious whether a problem comes from the stack (widths, separators) or from the query string (column names, types).
Ranges side by side instead of stacked
A comma inside the braces places ranges next to each other: {Names!A2:A, Scores!B2:B} makes a two-column array, provided both ranges have the same number of rows. This is useful when two columns of the same table live on different tabs, row for row. It is not a join — if the rows are in a different order, the pairs are wrong. Use a lookup for that.
Pivot across tabs
=QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C},
"select Col2, sum(Col3)
where Col1 is not null
group by Col2
pivot month(Col1)", 0)pivot turns each month into a column, so each product gets one row with January, February and March totals side by side. QUERY's month() counts from 0, so the column headers read 0, 1 and 2 for January to March. Col1 must hold real dates, not text that looks like dates, for month() to work.
Use a cell value in the query
To let people pick a product from a dropdown in F1, build the query string with &:
=QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C},
"select Col1, Col3 where Col2 = '"&F1&"' order by Col1", 0)Text values go inside single quotes; numbers don't. A value containing an apostrophe (O'Neil) breaks the string, so avoid them in dropdown lists or swap to a FILTER formula.
QUERY vs FILTER vs SUMIFS for multiple tabs
| Need | Best choice | Why |
|---|---|---|
| Grouped totals across tabs | QUERY over a stack | group by does it in one formula |
| All matching rows, no aggregation | FILTER(VSTACK(...), condition) | No query string to get wrong |
| One total for one criterion | SUMIFS per tab, added together | Simplest to read |
| Combine tables by ID | XLOOKUP or VLOOKUP | QUERY can't join |
For FILTER over a stack, refer to a column with INDEX: =FILTER(VSTACK(Jan!A2:C, Feb!A2:C), INDEX(VSTACK(Jan!A2:C, Feb!A2:C),,2)="Desk"). Wrapping the stack in LET avoids writing it twice.
Headers in stacked ranges
If you include the header row of the first range ({Jan!A1:C; Feb!A2:C}) and set the last argument to 1, QUERY treats the first row as headers, and label is only needed for aggregated columns. You still refer to columns as Col1, Col2 — the header text isn't used as a column name in the query string. Never include the header rows of the later ranges; they become data rows and turn number columns into mixed-type columns, which makes sum() ignore values.
Frequently asked questions
- Can Google Sheets QUERY use multiple ranges?
- Yes. Stack them in an array, such as {Sheet1!A2:C; Sheet2!A2:C}, and refer to columns as Col1, Col2 and so on.
- Why does QUERY say NO_COLUMN?
- With an array as the data, columns must be referenced as Col1, Col2… rather than A, B.
- Can QUERY join two tables?
- No. Use VLOOKUP or XLOOKUP to combine tables by a key.
- Can I use QUERY across tabs from different files?
- Yes. Put IMPORTRANGE calls inside the stack, such as {IMPORTRANGE(url1, "Sales!A2:C"); IMPORTRANGE(url2, "Sales!A2:C")}, and use Col1 notation.
- Why do some numbers disappear from my QUERY results?
- QUERY assigns one type per column based on the majority of values. Numbers in a mostly-text column, or text in a mostly-number column, come back blank. Clean the column so every value has the same type.