Analistable

How to use SUMIFS across multiple sheets

By the Analistable team · Updated · 2 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.

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