SUMIFS Across Multiple Sheets (Tabs)

Standard SUMIFS doesn't support 3D references across multiple worksheets. Combine SUMPRODUCT, SUMIFS, and an array of INDIRECT sheet names to solve this layout issue instantly.

Excel SUMIFS syntax (single sheet) vs this multi-sheet pattern

Searches like excel sumifs syntax usually point to the same core rule: SUMIFS always starts with the range you want to add up, then alternating criteria_range and criteria pairs. Excel's signature is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Example: sum column C where column A is "North" and column B is "Q1":

=SUMIFS(C:C, A:A, "North", B:B, "Q1")

That pattern works on one worksheet. If your data lives on separate tabs (January, February, March, or regions), native SUMIFS cannot reference Jan:Mar!C:C the way SUM might—so we build one SUMIFS per sheet with INDIRECT and collapse the results with SUMPRODUCT. This keeps the same logical filters (your criteria range and criteria) while repeating them across each sheet name you list.

For a plain one-sheet formula with form fields and copy-ready output, use the SUMIFS formula generator— this page focuses on the cross-tab variant.

How to use this generator (steps)

  1. List sheet names exactly as they appear on the tab bar, comma-separated (e.g. Jan, Feb, Mar). Spaces in names are fine; avoid trailing commas.
  2. Set the sum range on each sheet relative to that sheet—often a column of amounts (e.g. C:C or Sales!$E$2:$E$500 if you use the same layout on every tab).
  3. Set the criteria range to the column you filter on (e.g. product or region), matching the same column address on each sheet.
  4. Enter the condition as a quoted value ("Apple") or a cell reference ($F$1) so every sheet uses the same rule.
  5. Copy the generated formula into a summary workbook cell. If Excel asks for array confirmation on older versions, use Ctrl+Shift+Enter only when the formula is a legacy array type; the generator targets modern dynamic evaluation where possible.
  6. Prefer performance? On Excel 365 you can sometimes replace INDIRECT stacks with VSTACK of ranges; INDIRECT is still the most portable pattern for variable sheet lists.

Together, the syntax section and steps align this URL with long-tail queries about excel sumifs syntax while staying consistent with the actual tool output below.

SUMIFS Across Multiple Sheets Generator

Generate an INDIRECT + SUMPRODUCT array formula to summarize data spanning across multiple tabs.

The exact names of the tabs you want to sum across.

Generated Formula

=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"Jan","Feb","Mar"}&"'!B:B"), INDIRECT("'"&{"Jan","Feb","Mar"}&"'!A:A"), "Apple"))

How does the cross-sheet formula work?

When managing monthly or regional sheets (like "Jan," "Feb," "Mar"), you can't just type =SUMIFS(Jan:Mar!B:B...). Instead, we wrap an array of sheet names in the INDIRECT function.SUMIFS evaluates each sheet and returns an array of totals. SUMPRODUCT then adds up that array to give your final total.

For large datasets, newer Excel functions like VSTACK can be more performant than volatile INDIRECT functions. But SUMPRODUCT(SUMIFS(INDIRECT())) remains highly compatible across older versions and Google Sheets.