When each month (or store, or department) lives on its own identical sheet, a 3D reference totals the same cell across all of them in one short formula — no linking sheet by sheet.
Jan:Dec part is a sheet range — every tab between Jan and Dec is included, just like a cell range includes every cell between two corners.
The example
Three monthly sheets (Jan, Feb, Mar), each with the total in B2. A summary sheet adds them.
| A | B | |
|---|---|---|
| 1 | Sheet | B2 value |
| 2 | Jan | $100 |
| 3 | Feb | $140 |
| 4 | Mar | $120 |
| 5 | =SUM(Jan:Mar!B2) | $360 |
The formula
On the summary sheet:
How it works
A 3D reference treats stacked sheets like a third dimension:
Jan:Marnames the first and last sheet in the run; Excel includes every tab physically between them.!B2picks the same cell on each of those sheets.SUM(…)adds B2 from Jan, Feb, and Mar —360.- Drag a new sheet between Jan and Mar and it’s automatically included — the formula doesn’t change.
Use sandwich sheets. Put blank “Start” and “End” tabs around your monthly sheets and reference =SUM(Start:End!B2). Any sheet you drop between them is counted — foolproof for growing workbooks.
Try it: interactive demo
Toggle which monthly sheets exist; watch the 3D sum.
Variations
Other functions work in 3D too
AVERAGE, MAX, MIN, COUNT all accept sheet ranges:
Sum a whole range across sheets
Not just one cell — a block on each sheet:
Quote sheet names with spaces
Wrap the sheet range in apostrophes:
Pitfalls & errors
Sheets must be physically adjacent. A 3D reference includes every tab between the two named, in tab order. If a stray sheet sits between them, it’s included too — arrange tabs deliberately or use sandwich sheets.
The cell layout must match. 3D SUM adds the same cell on each sheet, so every sheet needs its total in the same place (B2 here).
3D references don’t work in SUMIFS/COUNTIFS. Those conditional functions can’t span sheets. Consolidate the data or use Power Query for cross-sheet conditional totals.
Practice workbook
Frequently asked questions
How do I sum the same cell across multiple sheets in Excel?
How do I make sure new sheets are included automatically?
Can I use SUMIFS across multiple sheets?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class