Monthly tabs, regional tabs, one tab per salesperson – at some point, the totals need to land in one place. The fastest way to sum values across multiple sheets in Excel is a 3D reference, which adds the same cell or range from every sheet in a row of tabs with a single formula. The steps below use a simple example, then cover non-adjacent sheets and conditional totals.
Key Takeaways
- A 3D reference such as =SUM(Jan:Mar!B2) adds the same cell across every sheet from the first to the last named tab.
- Sheet names with spaces must be wrapped in single quotes, for example =SUM(‘Jan Sales:Mar Sales’!B2).
- For sheets that are not side by side, list each sheet reference separated by commas inside SUM.
- For conditional totals, add one SUMIF per sheet, because SUMIF does not accept 3D references.
Step-by-Step Guide to Sum Values Across Multiple Sheets in Excel
The example below assumes three sheets named Jan, Feb, and Mar, each with that month’s sales total in cell B2, plus a sheet named Summary where the grand total will appear.
Method 1: Sum the Same Cell Across Adjacent Sheets
- Step 1: Click the Summary sheet tab and select the cell where the total should appear, for example B2.
- Step 2: Type =SUM( to start the formula.
- Step 3: Click the Jan sheet tab, then hold down Shift and click the Mar sheet tab. Every sheet from Jan to Mar is now grouped inside the formula.
- Step 4: Click cell B2 on the Jan sheet. The formula bar now reads =SUM(Jan:Mar!B2.
- Step 5: Type the closing parenthesis and press Enter. The finished formula is =SUM(Jan:Mar!B2), and the cell shows the combined total of B2 from all three sheets.

Tip: Typing the formula by hand works just as well. If a sheet name contains spaces, wrap the whole sheet span in single quotes, like =SUM(‘Jan Sales:Mar Sales’!B2). Without the quotes, Excel returns an error.
Method 2: Sum a Range Across Multiple Sheets
- Step 1: Select the cell on the Summary sheet that will hold the result.
- Step 2: Type =SUM( and click the first sheet tab, hold Shift, and click the last sheet tab.
- Step 3: On that sheet, drag across the range to total, for example B2:B10.
- Step 4: Close the parenthesis and press Enter. The formula =SUM(Jan:Mar!B2:B10) adds every value in B2:B10 on all three sheets.
Method 3: Sum Sheets That Are Not Next to Each Other
A 3D reference only covers tabs that sit side by side. When the sheets are scattered, or only some of them should count, list each one separately.
- Step 1: Select the result cell on the Summary sheet and type =SUM(.
- Step 2: Click the first sheet tab, click the cell to add, and type a comma.
- Step 3: Repeat for each remaining sheet, so the formula looks like =SUM(Jan!B2,Mar!B2,Jun!B2).
- Step 4: Press Enter to get the total.
Method 4: Sum Values Across Sheets Based on a Condition
Say each sheet lists products in column A and sales in column B, and the Summary sheet needs the total for one product. SUMIF cannot take a 3D reference, so each sheet gets its own SUMIF, joined with plus signs.
- Step 1: On the Summary sheet, type the product name in cell A2, for example Keyboard.
- Step 2: In B2, enter =SUMIF(Jan!A:A,A2,Jan!B:B)+SUMIF(Feb!A:A,A2,Feb!B:B)+SUMIF(Mar!A:A,A2,Mar!B:B).
- Step 3: Press Enter. Excel returns the total sales of the product in A2 across all three sheets.
- Step 4: Change the product name in A2 to see the total for any other product.
Tip: Adding blank sheets named Start and End on either side of the month tabs, and using =SUM(Start:End!B2), keeps new tabs included automatically. Any sheet dragged between Start and End joins the total with no formula edits.
Common Problems and Fixes
The Formula Returns a #NAME? Error
This usually means a sheet name is misspelled or a name containing spaces is missing its single quotes. Check the spelling against the sheet tab and wrap spaced names, as in ‘Jan Sales:Mar Sales’!B2.
The Total Is Smaller Than Expected
Values stored as text are ignored by SUM. Look for numbers aligned to the left or marked with a small green triangle, then convert them to real numbers. Also confirm that every sheet to be counted sits between the first and last tab named in the formula, because a sheet moved outside that span drops out of the total.
A #REF! Error Appears After Deleting a Sheet
Removing a sheet that is referenced individually, as in Method 3, breaks that part of the formula. Rewrite the formula without the deleted sheet. With a 3D reference, deleting a sheet inside the span simply removes it from the total.
A Circular Reference Warning Appears
This happens when the Summary sheet sits between the first and last tab of the 3D span and the formula points at its own cell. Move the Summary sheet outside the span, for example to the far left.
The Same Formula Gives Wrong Numbers on Some Sheets
A 3D reference adds whatever sits in the same cell address on every sheet. If one sheet has its total in a different cell, that sheet contributes the wrong value. Align the layout, or use the individual references from Method 3.
Frequently Asked Questions
What is the formula to sum values across multiple sheets in Excel?
The simplest formula is =SUM(FirstSheet:LastSheet!CellAddress), for example =SUM(Jan:Mar!B2). It adds cell B2 from every sheet between Jan and Mar, including both of them.
Can a range be summed across several sheets at once?
Yes. Use a range in the reference, such as =SUM(Jan:Mar!B2:B10). Excel adds every value in B2:B10 on each sheet from Jan to Mar.
How can sheets that are not next to each other be summed?
List each sheet reference separated by commas, such as =SUM(Jan!B2,Mar!B2,Jun!B2). A 3D reference only works for tabs that sit side by side.
Do sheet names with spaces need quotes?
Yes. Wrap the sheet name or sheet span in single quotes, such as =SUM(‘Jan Sales:Mar Sales’!B2). Without them, Excel cannot read the reference.
Does SUMIF work across multiple sheets with a 3D reference?
No. SUMIF does not accept 3D references. Add one SUMIF per sheet and join them with plus signs, such as =SUMIF(Jan!A:A,A2,Jan!B:B)+SUMIF(Feb!A:A,A2,Feb!B:B).
Will a new sheet be included in the total automatically?
It will if the new sheet is placed between the first and last sheets named in the 3D reference. Sheets added outside that span are not counted.
Which other functions support 3D references?
Functions such as AVERAGE, COUNT, COUNTA, MAX, MIN, and PRODUCT also accept 3D references, so the same sheet-span pattern works for them.
Why does the sum across sheets show a wrong or zero result?
The most common cause is numbers stored as text, which SUM ignores. A cell address that holds different data on different sheets can also skew the total.
