Analistable

How to use SUMIFS across multiple sheets

By the Analistable team · Updated · 5 min read

SUMIFS doesn't accept 3D references like Jan:Dec!C:C. In Microsoft 365, stack the sheets instead: =SUM(FILTER(VSTACK(Jan:Dec!C2:C500), VSTACK(Jan:Dec!A2:A500)="Desk")). In older versions, list the sheet names in a range called Sheets and use =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C:C"), INDIRECT("'"&Sheets&"'!A:A"), "Desk")). For the same cell on every sheet, a plain 3D SUM works.

Part of our guide: How to consolidate data in Excel

Method 1: 3D SUM (same cell on every sheet)

=SUM(Jan:Dec!B5)

Adds B5 on every sheet from Jan to Dec in tab order. Sheets moved between Jan and Dec are included automatically. No criteria are possible — the layout must put the same item in the same cell everywhere.

Tip: add two empty sheets called Start and End around the sheets to total, and use =SUM(Start:End!B5). Drag a sheet between them to include it, outside them to exclude it.

Method 2: FILTER + VSTACK (Microsoft 365)

=SUM(FILTER(VSTACK(Jan:Dec!C2:C500), VSTACK(Jan:Dec!A2:A500)=F2, 0))

VSTACK accepts 3D references and stacks the column from every sheet; FILTER keeps the amounts whose product matches F2. Add conditions by multiplying them:

=LET(prod, VSTACK(Jan:Dec!A2:A500), region, VSTACK(Jan:Dec!B2:B500), amt, VSTACK(Jan:Dec!C2:C500),
     SUM(FILTER(amt, (prod=F2) * (region=G2), 0)))

Method 3: SUMPRODUCT + INDIRECT (any version)

  1. List the sheet names in a range (for example H2:H13) and name it Sheets (Formulas → Define Name).
  2. Use:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C2:C500"), INDIRECT("'"&Sheets&"'!A2:A500"), F2))

SUMIFS runs once per sheet name and SUMPRODUCT adds the results. The quotes around the sheet name handle names with spaces. Downsides: INDIRECT is volatile (recalculates on every change) and breaks silently if a sheet is renamed without updating the list.

Desk sales across 12 monthly sheets
ProductMethodResult
DeskFILTER + VSTACK3200
DeskSUMPRODUCT + INDIRECT3200
DeskPivot on stacked data3200

When to stop using formulas

If you're summing across many sheets with several criteria, the data probably belongs in one table. Stack the sheets (Power Query Append or VSTACK) and use one SUMIFS or a pivot table — simpler, faster and easier to check. See combining sheets into one and the Consolidate function.

Which method should you use?

  • Same cell on every sheet, no criteria → 3D SUM.
  • Criteria, Microsoft 365 → FILTER + VSTACK (no volatile functions, accepts 3D references).
  • Criteria, older Excel → SUMPRODUCT + SUMIFS + INDIRECT with a sheet list.
  • Many criteria, many sheets → stack the data once and use a pivot table.

Example layout

Each monthly sheet has Product in column A, Region in column B and Amount in column C. The summary sheet lists products in F2:F10. In G2, =SUM(FILTER(VSTACK(Jan:Dec!C2:C500), VSTACK(Jan:Dec!A2:A500)=F2, 0)) returns that product's total for the year; fill it down.

Worked example: three months, two criteria

Sheets Jan, Feb and Mar each hold Product, Region and Amount. You want Desk sales in the North.

Matching rows on each sheet
SheetProductRegionAmount
JanDeskNorth420
JanDeskNorth210
FebDeskNorth630
MarDeskNorth420

Other rows (Desk in the South, Chairs in the North) are excluded. Per sheet, SUMIFS returns 630 for Jan, 630 for Feb and 420 for Mar; the cross-sheet total is 1,680. Both formulas give the same answer:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C2:C500"), INDIRECT("'"&Sheets&"'!A2:A500"), "Desk", INDIRECT("'"&Sheets&"'!B2:B500"), "North"))

To check the result, put a plain =SUMIFS(C:C, A:A, "Desk", B:B, "North") on each sheet and add the three cells by hand.

A per-sheet breakdown

Totals by month are often more useful than one number. Next to the list of sheet names in H2:H13, enter =SUMIFS(INDIRECT("'"&H2&"'!C2:C500"), INDIRECT("'"&H2&"'!A2:A500"), $F$2) and fill down. The column sums to the same total as the SUMPRODUCT version, and a zero shows immediately which month has no matching rows or a typo in its sheet name.

COUNTIFS and AVERAGEIFS across sheets

The same patterns work for counting: swap SUMIFS for COUNTIFS inside SUMPRODUCT. Averages need care — an average of each sheet's average is wrong when sheets have different numbers of rows. Divide the cross-sheet sum by the cross-sheet count instead, or in Microsoft 365 use =AVERAGE(FILTER(VSTACK(Jan:Dec!C2:C500), VSTACK(Jan:Dec!A2:A500)=F2)).

Google Sheets

Google Sheets has no 3D references, so Jan:Dec!B5 isn't valid. Stack the ranges with braces and sum with criteria: =SUM(FILTER({Jan!C2:C500; Feb!C2:C500; Mar!C2:C500}, {Jan!A2:A500; Feb!A2:A500; Mar!A2:A500}=F2)). INDIRECT with a sheet list works too. More options: SUMIF across multiple sheets in Google Sheets.

Troubleshooting

When the cross-sheet total is wrong
SymptomCauseFix
Total is too lowA sheet sits outside Jan:Dec in tab orderMove it between the first and last sheet, or use Start/End markers
#REF! from INDIRECTSheet name in the list doesn't match the tabCheck spelling and spaces; the quotes handle spaces
Total is 0Amounts stored as textConvert the column to numbers
#CALC! with FILTERNo matching rows and no third argumentAdd 0 as FILTER's third argument
Workbook is slowINDIRECT on whole columns recalculates constantlyUse bounded ranges or switch to FILTER + VSTACK

Using wildcards and dates as criteria

Criteria behave the same way across sheets as on one sheet. "Desk*" matches Desk, Desk Large and Desk Oak; with FILTER, use ISNUMBER(SEARCH("Desk", prod)) instead, because FILTER compares exact values. For a date window, add two criteria to SUMIFS such as ">="&$J$1 and "<="&$J$2, where J1 and J2 hold real dates rather than text. In the FILTER version, multiply the tests: (dates>=J1)*(dates<=J2).

Repeating it every month

With the Start and End markers, a new month means copying last month's sheet, renaming it and dropping it between the markers; FILTER + VSTACK picks it up immediately. With INDIRECT, also add the new name to the Sheets list — or make the list a table so the named range grows with it. If the source sheets arrive as separate monthly files, stacking them with Power Query from a folder avoids cross-sheet formulas altogether.

Quick test

Change one matching amount on a single sheet and confirm the cross-sheet total moves by exactly that amount.

Frequently asked questions

Does SUMIFS work across multiple sheets?
Not with a 3D reference. Use SUMPRODUCT with INDIRECT and a list of sheet names, or SUM with FILTER and VSTACK in Microsoft 365.
How do I sum the same cell across sheets?
Use a 3D reference: =SUM(FirstSheet:LastSheet!B5).
Why does my INDIRECT formula return #REF!?
A sheet name in the list doesn't match an actual sheet, often because of a typo or a rename.

Related guides