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)
- List the sheet names in a range (for example H2:H13) and name it
Sheets(Formulas → Define Name). - 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.
| Product | Method | Result |
|---|---|---|
| Desk | FILTER + VSTACK | 3200 |
| Desk | SUMPRODUCT + INDIRECT | 3200 |
| Desk | Pivot on stacked data | 3200 |
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.
| Sheet | Product | Region | Amount |
|---|---|---|---|
| Jan | Desk | North | 420 |
| Jan | Desk | North | 210 |
| Feb | Desk | North | 630 |
| Mar | Desk | North | 420 |
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
| Symptom | Cause | Fix |
|---|---|---|
| Total is too low | A sheet sits outside Jan:Dec in tab order | Move it between the first and last sheet, or use Start/End markers |
| #REF! from INDIRECT | Sheet name in the list doesn't match the tab | Check spelling and spaces; the quotes handle spaces |
| Total is 0 | Amounts stored as text | Convert the column to numbers |
| #CALC! with FILTER | No matching rows and no third argument | Add 0 as FILTER's third argument |
| Workbook is slow | INDIRECT on whole columns recalculates constantly | Use 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.