Can I use standard SUMIFS across multiple sheets?

No, SUMIFS natively does not support 3D referencing across multiple sheets. You must use SUMPRODUCT bundled with INDIRECT sheet arrays or use VSTACK in modern Excel to stack the ranges first.

SUMIFS Across Multiple Sheets Generator | SheetMaster

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.

SUMIFS Across Multiple Sheets Generator

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

Sheet Names (Comma separated)

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

Sum Range

Criteria Range

Condition (Criteria)

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.