Analistable

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.
  • Col1 is the first column of the stacked array, whatever its letter in the source.
  • where Col1 is not null removes the empty rows that open-ended ranges add.
  • The last argument 0 says the data has no header row.
Result: units by product across three tabs
ProductUnits
Chair96
Desk41
Lamp27

Common errors

QUERY errors with stacked ranges
ErrorCauseFix
Unable to parse query string… NO_COLUMN: AUsing A, B with an arrayUse Col1, Col2…
In ARRAY_LITERAL, an Array Literal was missing valuesRanges have different widthsMake every range the same number of columns
Numbers missing in sum()A column mixes text and numbersQUERY uses the majority type per column; clean the column
Formula parse errorLocale separatorsSome 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

  1. Check that every tab has the same columns in the same order — here Date, Product, Units in A:C.
  2. 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.
  3. Wrap it in QUERY with select * and the not-null filter to confirm the gaps disappear.
  4. Replace select * with the columns and aggregation you need.
  5. Add label clauses 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

Which function to use
NeedBest choiceWhy
Grouped totals across tabsQUERY over a stackgroup by does it in one formula
All matching rows, no aggregationFILTER(VSTACK(...), condition)No query string to get wrong
One total for one criterionSUMIFS per tab, added togetherSimplest to read
Combine tables by IDXLOOKUP or VLOOKUPQUERY 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.

Related guides