Does Google Sheets support 3D references like Sheet1:Sheet3!A1?

No. Add the sheets' formulas together, stack the ranges in an array, or loop over tab names with MAP and INDIRECT.

How do I SUMIF across all tabs in Google Sheets?

List the tab names and use SUM(MAP(list, LAMBDA(tab, SUMIF(INDIRECT(...), criterion, INDIRECT(...))))).

Can QUERY sum across multiple sheets?

Yes. Stack the sheets inside { ; } and use select sum(ColN) with a where or group by clause.

Why does my QUERY across tabs return an error about columns?

Every range inside { ; } must have the same number of columns. Check that each tab's range, such as A2:C, has the same width.

Can I SUMIF across sheets in different Google Sheets files?

Yes, but bring each file in first with IMPORTRANGE, then stack and sum the imported ranges. Each file needs access granted once.

Is MAP available in every Google Sheets account?

MAP and LAMBDA are part of current Google Sheets for personal and Workspace accounts. If a formula shows an unknown function error, use the added-SUMIFs or QUERY approach.

How to SUMIF across multiple sheets in Google Sheets

By the Analistable team · Updated 2026-10-07 · 5 min read

Google Sheets has no 3D references, so Jan:Dec!A:A doesn't work. For a few tabs, add SUMIFs together: =SUMIF(Jan!A:A, "Desk", Jan!C:C) + SUMIF(Feb!A:A, "Desk", Feb!C:C). For many tabs, stack them and use QUERY: =QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C}, "select sum(Col3) where Col1 = 'Desk'", 0). With a list of tab names, MAP + INDIRECT keeps the formula short.

Part of our guide: How to combine data in Google Sheets

Option 1: add SUMIFs (a few tabs)

=SUMIF(Jan!A:A, "Desk", Jan!C:C)
 + SUMIF(Feb!A:A, "Desk", Feb!C:C)
 + SUMIF(Mar!A:A, "Desk", Mar!C:C)

Clear and fast, but every new tab means editing the formula.

Option 2: QUERY over stacked tabs

=QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C},
  "select Col1, sum(Col3) where Col1 is not null group by Col1 label sum(Col3) 'Total'", 0)

This returns a total for every product at once. Use Col1, Col2… inside QUERY when the data is a stacked array. Details: QUERY with multiple ranges.

Result
Product	Total
Chair	96
Desk	41
Lamp	27

Option 3: a list of tab names

Put the tab names in Sheets!A2:A13, then:

=SUM(MAP(Sheets!A2:A13, LAMBDA(tab,
  IF(tab = "", 0, SUMIF(INDIRECT("'" & tab & "'!A:A"), "Desk", INDIRECT("'" & tab & "'!C:C"))))))

MAP runs the SUMIF once per tab name, and SUM adds the results. Adding a month means adding its name to the list. INDIRECT can't take a whole array of names on its own, which is why MAP is needed.

Multiple conditions

Swap SUMIF for SUMIFS in options 1 and 3 (SUMIFS(sum_range, range1, criterion1, range2, criterion2)), or add conditions to QUERY's where clause: where Col1 = 'Desk' and Col2 = 'North'.

The Excel equivalent — where 3D SUM works but SUMIFS doesn't — is covered in SUMIFS across multiple sheets in Excel.

Which option to choose

Two or three tabs that rarely change → add SUMIFs together.
Totals for many products at once → QUERY over the stacked tabs.
A growing list of monthly tabs → MAP with INDIRECT and a list of tab names.
Tabs in other spreadsheet files → IMPORTRANGE each file into the stack, then QUERY.

Common errors

#REF! from INDIRECT: a tab name in the list doesn't match exactly (watch trailing spaces).
QUERY returns nothing: the criterion's case differs ('desk' vs 'Desk') — QUERY string comparisons are case-sensitive; use lower(Col1) = 'desk'.
Totals too low: open-ended ranges are fine, but make sure every tab's amounts are numbers, not text.

Worked example

Three tabs, Jan, Feb and Mar, each list Product in column A, Region in B and Units in C. The per-tab totals are:

Units by product and tab
Product	Jan	Feb	Mar	Total
Chair	30	34	32	96
Desk	12	15	14	41
Lamp	9	8	10	27

Options 1 and 3 with "Desk" both return 12 + 15 + 14 = 41, and the QUERY in option 2 returns all three totals at once, matching the earlier result table. The grand total across tabs is 96 + 41 + 27 = 164, a handy check: =SUM(Jan!C2:C) + SUM(Feb!C2:C) + SUM(Mar!C2:C) should give the same figure.

Criteria from a cell

Put the product in a cell such as F1 so the formula can be reused:

=SUM(MAP(Sheets!A2:A13, LAMBDA(tab,
  IF(tab = "", 0, SUMIFS(INDIRECT("'" & tab & "'!C:C"),
                         INDIRECT("'" & tab & "'!A:A"), $F$1,
                         INDIRECT("'" & tab & "'!B:B"), $G$1)))))

In QUERY, join the cell into the query string: "select sum(Col3) where Col1 = '" & F1 & "'". An apostrophe in the product name breaks the query string, so use SUMIFS for names like “Children's desk”.

Troubleshooting

Symptom, cause and fix
Symptom	Cause	Fix
#REF! “Unresolved sheet name”	A tab name in the list is misspelt or has been renamed	Fix the list; wrap names in TRIM
QUERY returns a header but no total	No rows meet the where clause, or the column has mixed text and numbers	Check spelling and case; make the column numeric
Array result was not expanded	Cells below the formula aren't empty	Clear the cells the result needs
Formula is slow	INDIRECT on whole columns across many tabs	Use bounded ranges such as A2:C2000

Doing it every month

Add each new month as a tab with the same layout and type its name into the tab list; option 3 then includes it with no formula changes. If the months live in separate files instead, bring them in with IMPORTRANGE before summing, as shown in combining multiple IMPORTRANGE formulas, or stack them once into a single long table with a Month column, after which a plain SUMIFS or a pivot table answers every question.

Stack once, sum anywhere

If you keep adding SUMIF formulas across tabs, consider a single combined tab instead. Put this on a tab called All:

={ QUERY(Jan!A2:C, "select 'Jan', A, B, C where A is not null label 'Jan' ''", 0);
   QUERY(Feb!A2:C, "select 'Feb', A, B, C where A is not null label 'Feb' ''", 0);
   QUERY(Mar!A2:C, "select 'Mar', A, B, C where A is not null label 'Mar' ''", 0) }

Each QUERY adds the month as a constant first column and drops empty rows. After that, any total is an ordinary =SUMIFS(All!D:D, All!B:B, "Desk"), a pivot table works, and adding a month means adding one line to the array.

Frequently asked questions

Does Google Sheets support 3D references like Sheet1:Sheet3!A1?: No. Add the sheets' formulas together, stack the ranges in an array, or loop over tab names with MAP and INDIRECT.
How do I SUMIF across all tabs in Google Sheets?: List the tab names and use SUM(MAP(list, LAMBDA(tab, SUMIF(INDIRECT(...), criterion, INDIRECT(...))))).
Can QUERY sum across multiple sheets?: Yes. Stack the sheets inside { ; } and use select sum(ColN) with a where or group by clause.
Why does my QUERY across tabs return an error about columns?: Every range inside { ; } must have the same number of columns. Check that each tab's range, such as A2:C, has the same width.
Can I SUMIF across sheets in different Google Sheets files?: Yes, but bring each file in first with IMPORTRANGE, then stack and sum the imported ranges. Each file needs access granted once.
Is MAP available in every Google Sheets account?: MAP and LAMBDA are part of current Google Sheets for personal and Workspace accounts. If a formula shows an unknown function error, use the added-SUMIFs or QUERY approach.