How to Sum the Same Cell Across Sheets in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
To sum across sheets in Excel, use a 3D reference: =SUM(Jan:Mar!B2) adds cell B2 on the Jan sheet, the Mar sheet and every sheet between them. Type =SUM(, click the first tab, Shift+click the last tab, click the cell and press Enter. It only works when every sheet has the same layout. SUMIF doesn't accept 3D references.
Key takeaways
- =SUM(First:Last!B2) adds the same cell on every sheet from First to Last, by tab order.
- Sheets moved between the first and last tab are included; sheets moved outside are dropped.
- Put sheet names with spaces in single quotes: =SUM('Week 1:Week 4'!B2).
- Blank Start and End sheets make a bookend range you can drop new sheets into.
- 3D references work with SUM, AVERAGE, COUNT, MAX and MIN, but not with SUMIF or VLOOKUP.
On this page+
To sum across sheets in Excel, use a 3D reference such as =SUM(Jan:Mar!B2). It adds the same cell on every sheet from the first tab you name to the last, so a quarterly or yearly total takes one short formula. This guide shows how to build it, how to add new sheets safely, and what to do when you need conditions, which 3D references can't handle.
What is a 3D reference?#
A normal reference points at cells on one sheet, like Jan!B2. A 3D reference points at the same cells on a range of sheets: Jan:Mar!B2 means "B2 on Jan, on Mar, and on every sheet whose tab sits between them". The sheets are the third dimension, after rows and columns.
The examples use three sheets, Jan, Feb and Mar, with the same layout. Each lists products in A2:A6 and quantities in column B:
| Product | Jan (B) | Feb (B) | Mar (B) |
|---|---|---|---|
| Pens (row 2) | 120 | 140 | 110 |
| Paper (row 3) | 80 | 95 | 70 |
| Ink (row 4) | 45 | 50 | 60 |
| Tape (row 5) | 30 | 25 | 35 |
| Folders (row 6) | 0 | 60 | 40 |
How to sum the same cell across sheets#
On a Summary sheet, build the formula by clicking, so Excel writes the sheet names for you:
- Click the cell for the result and type
=SUM(. - Click the Jan tab.
- Hold Shift and click the Mar tab. All three tabs are now selected.
- Click cell B2.
- Type
)and press Enter.
The formula reads:
=SUM(Jan:Mar!B2)The result is 120 + 140 + 110 = 370 pens for the quarter. To total a whole range instead of one cell, select it in step 4: =SUM(Jan:Mar!B2:B6) returns 960, all products for all three months. The SUM function guide covers the basics of SUM itself.
How Excel decides which sheets to include#
Excel uses the tab order, not the sheet names. Everything between the first and last tab in the reference is included. That has some useful and some dangerous effects:
- Move a sheet in: drag an Apr tab between Jan and Mar, and its B2 joins the total.
- Move a sheet out: drag Feb to the right of Mar, and February disappears from the total.
- Delete the first or last sheet: delete Jan, and the reference becomes
Feb:Mar!B2. Excel adjusts it. - Hidden sheets between the two tabs still count.
So keep the monthly sheets together, and put the Summary sheet outside the range, usually first. If a total suddenly jumps or drops after someone tidies the tabs, check the tab order before you check the numbers.
Use bookend sheets for a total that grows#
To add months during the year without editing the formula, create two blank sheets called Start and End, and put all monthly sheets between them:
=SUM(Start:End!B2)When April arrives, drag its tab anywhere between Start and End, and it's included. The blank bookend sheets add nothing to the total. Many people colour those two tabs so nobody types data on them.
Sheet names with spaces and other tips#
If a sheet name contains spaces or symbols, Excel wraps the sheet part in single quotes:
=SUM('Week 1:Week 4'!B2)Clicking the tabs adds the quotes for you. A few more tips:
- Switch sheets fast with Ctrl+Page Down and Ctrl+Page Up on Windows, or Option+→ and Option+← on a Mac.
- Other functions work too. AVERAGE, COUNT, COUNTA, MAX, MIN and PRODUCT all accept 3D references.
=MAX(Jan:Mar!B2)returns 140, the best month for pens. - A quick shortcut in Excel for Windows: typing
=SUM('*'!B2)makes Excel replace the*with every sheet except the one you're on. - Explicit references like
=Jan!B2+Feb!B2+Mar!B2give the same 370. They're longer, but they don't change if tabs move.
How to sum across sheets with a condition#
SUMIF, COUNTIF, SUMIFS and VLOOKUP don't accept 3D references. If the products are in a different order on each sheet, =SUM(Jan:Mar!B2) would add the wrong rows, and you need a condition instead.
The simple way is one SUMIF per sheet, added together:
=SUMIF(Jan!A2:A6,"Pens",Jan!B2:B6)+SUMIF(Feb!A2:A6,"Pens",Feb!B2:B6)+SUMIF(Mar!A2:A6,"Pens",Mar!B2:B6)This returns 370 even if Pens is in row 4 on one sheet and row 2 on another. With many sheets, it gets long, and it's usually better to combine the data into one list first and use a single SUMIF or a pivot table. The guide to combining data from multiple sheets shows how. If the combined list needs filtering first, see how to sum FILTER results.
Why is my sum across sheets wrong?#
Check these common causes:
- Different layouts. If Ink is in row 4 on Jan but row 5 on Feb, a 3D SUM adds different products. Make every sheet identical, or use SUMIF.
- A sheet moved by accident. Look at the tab order between the first and last sheet.
- Numbers stored as text. SUM skips text, even if it looks like a number. The guide to a SUM that gives the wrong total explains how to spot and fix this.
- The summary sheet is inside the range. Then the formula can include its own cell and cause a circular reference. Move the summary tab outside.
- Filtered rows. A 3D SUM adds hidden rows too. SUBTOTAL can skip them on one sheet; see SUBTOTAL vs SUM.
For more on SUM's behaviour with blanks and text, read the complete guide to the SUM function.
Practise it#
Quarterly and yearly totals across monthly tabs are a classic task in admin and finance tests. Practise worksheets and cross-sheet references in a live spreadsheet, including a total across monthly tabs, with instant checks. Then practise SUM and AutoSum to speed up the totals on each sheet.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Here each month sits in its own column, standing in for three monthly sheets. In G2, use SUM to add the Pens figures for January, February and March in B2:D2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Mar | |||||||
| 2 | Pens, Q1 | |||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 |
Tip: Select the three cells in row 2, from B2 to D2.
Practise this in a lesson
Frequently asked questions
How do I sum the same cell across multiple sheets in Excel?
Use a 3D reference such as =SUM(Jan:Dec!B2). It adds B2 on every sheet from Jan to Dec, including the sheets between them in tab order.
How do I sum across sheets with sheet names that have spaces?
Wrap the sheet range in single quotes: =SUM('Week 1:Week 4'!B2). Excel adds the quotes for you if you build the formula by clicking the tabs.
Can I use SUMIF across multiple sheets?
Not with a 3D reference. Add one SUMIF per sheet, such as =SUMIF(Jan!A2:A6,"Pens",Jan!B2:B6)+SUMIF(Feb!A2:A6,"Pens",Feb!B2:B6), or combine the data on one sheet first.
How do I add a new sheet to a 3D SUM?
Drag the new sheet's tab so it sits between the first and last sheet in the range. The formula includes it at once, with no edit needed.
Why is my sum across sheets wrong?
The sheets probably don't share the same layout, so the same cell holds different things. A sheet may also have been moved into or out of the range by accident. Check the tab order and the cell on each sheet.