Excel Sum Across Sheets: Formulas and 3D References

Formulas & functions · Updated November 18, 2026

To sum the same cell across consecutive worksheets in Excel, use a 3D reference such as =SUM(Jan:Mar!B2). If the sheets are not next to one another, list each sheet reference separately inside SUM; the right method depends on how your workbook is arranged.

What it means to sum across sheets

A formula that sums across sheets combines values stored on different tabs in one Excel workbook. The most common case is a workbook with one worksheet per month, department, location, or reporting period, where each sheet uses the same layout. You might want one annual total from the monthly sales value in cell B2, for example.

Excel’s SUM function can add values from multiple worksheets. When the sheets are consecutive in the tab order, a 3D reference lets you name the first and last sheets and a cell or range to add. “3D” refers to the reference spanning multiple worksheets, not to a three-dimensional chart or a special data type.

For three worksheets named Jan, Feb, and Mar, assume each month’s sales total is in B2: Jan contains 120, Feb contains 95, and Mar contains 135. The formula =SUM(Jan:Mar!B2) returns 350. The same pattern works for a matching range, such as a block of product amounts, provided the range occupies the same addresses on every sheet.

How a 3D reference works

In =SUM(Jan:Mar!B2), Jan and Mar mark the beginning and end of a sheet span, while B2 identifies the cell to sum on each sheet in that span. Excel includes every worksheet between the two named sheets in the workbook’s tab order. It does not select sheets based on their names or dates.

That behavior makes the formula convenient for a series of similarly structured tabs, but it also means worksheet order matters. If Apr is placed between Jan and Mar, Apr becomes part of the sum. Moving a sheet into or out of the span can change the results without changing the visible formula. Sheets before Jan or after Mar are not included.

To enter one, select the cell for the result, type the formula, and press Enter. You can also start typing =SUM(, click the first sheet tab, hold Shift while clicking the last sheet tab, select the desired cell or range, and finish the formula. Excel will insert a reference based on the selected sheets and address. Check the completed expression before relying on it, especially if the workbook has many tabs.

Set up worksheets for reliable totals

3D references work best when every sheet has the same structure. Put the figure to be combined in the same cell on every worksheet, and use consistent units and meanings. For example, B2 should represent monthly sales on every month tab, not sales on one sheet and expenses on another.

Before writing the total, check these practical details:

  • Give the relevant worksheets clear names, such as Jan through Dec or East through West.
  • Keep the sheets to be included together in the tab order, with the intended first and last sheets at the boundaries.
  • Use matching cell addresses and compatible data types across the worksheets.
  • Keep explanatory notes, summary tabs, and other sheets outside the span unless they should contribute values.
  • Confirm that each included cell contains a number or a formula returning a number, rather than text that only looks numeric.

A summary worksheet can sit outside the endpoints. For instance, if a Summary tab is positioned before Jan and the monthly sheets run from Jan to Dec, a 3D reference from Jan through Dec excludes Summary. This helps avoid accidentally adding a total back into itself. If you later move Summary between the endpoints, however, the span will include it, so verify tab order after reorganizing the workbook.

Sum a range across every sheet in a span

You can use a 3D reference for a range, not just a single cell. Assume Jan, Feb, and Mar each have daily amounts in B2:B4. If those amounts are 10, 20, and 30 on Jan; 5, 15, and 25 on Feb; and 1, 2, and 3 on Mar, then =SUM(Jan:Mar!B2:B4) returns 111. The calculation adds all nine cells across the three worksheets.

A range reference is useful when each sheet contains a list of transactions, staff hours, or item quantities in a fixed area. SUM ignores blank cells and text entries in referenced cells, and adds numeric values. If some sheets have more rows than others, the formula still uses the same specified addresses on each sheet; it will not automatically find the last populated row beyond the range you chose.

For a monthly total by row, a 3D range can be placed in a summary formula for each matching row. For example, if each sheet has product names in A2:A10 and values in B2:B10, summing B2 across the month span gives the combined amount for the item represented by row 2. Copying that formula down changes B2 to B3, B4, and so on, as ordinary relative references do. Make sure the item order is consistent; otherwise, row 2 may represent different products on different tabs.

When sheets are not consecutive

A 3D reference necessarily includes the sheets between its endpoints. If you need to exclude an intervening worksheet, or add values from tabs separated in the workbook, use individual references as SUM arguments instead.

Assume Jan!B2 contains 120 and Mar!D8 contains 40, and you want those two values only—not anything on the worksheet between them. The formula =SUM(Jan!B2,Mar!D8) returns 160. This also shows that individual references can point to different cells, which a single 3D range cannot do.

Sheet names containing spaces or certain punctuation need single quotation marks around the name in a reference. For example, suppose North Region!B2 contains 70 and South Region!B2 contains 30. The formula =SUM('North Region'!B2,'South Region'!B2) returns 100. Excel often adds the quotation marks automatically when you build a formula by clicking cells, but they are important when typing such references yourself.

For many worksheets that are not contiguous, a long list of individual references can become difficult to maintain. Consider whether the workbook can be reorganized into a clear sheet span, or whether a consolidated table would be easier to manage. Avoid using a 3D reference merely because the sheets happen to be adjacent if they do not share the same meaning and layout.

Choose the right approach for your workbook

Use a 3D reference when all of the following are true: the sheets are part of one ordered series, the same cell or range means the same thing on every sheet, and all sheets between the endpoints should be included. Monthly reports with a consistent template are a typical fit.

Use individual sheet references when you need selected tabs only, the values occupy different cells, or there are unwanted worksheets between the tabs you need. For a large workbook that changes often, a central data table may be better than adding more tabs. A table with a date, category, and amount column can be filtered, summarized with a PivotTable, or analyzed with formulas designed around the consolidated records.

One common misconception is that the formula =SUM(Jan:Mar!B2) means “sum the sheets whose names fall alphabetically between Jan and Mar.” It means “sum the sheets located between Jan and Mar in tab order.” Another is that adding a new sheet anywhere will always update the result. A sheet added between the endpoints is included; one added outside them is not. The formula’s boundaries, not a month naming convention, determine the span.

Common problems and ways to fix them

  • The total is too high: Look for an unintended worksheet between the endpoints, a duplicated value, or a summary tab included in the span. Check the tabs in order, not just their names.
  • The total is too low: Confirm the first and last sheet names, the cell address, and whether all intended tabs lie inside the span. A new sheet placed after the ending tab will not be included.
  • The result is zero or missing values: Check whether the entries are stored as numbers rather than text. Text values are not added by SUM, even when they appear to contain digits. Also check for numbers entered in a different cell on some sheets.
  • The formula shows an error: Verify that sheet names are spelled correctly and that names with spaces are enclosed in single quotes. A deleted or renamed referenced sheet can also break a reference.
  • The copied formula changes unexpectedly: Relative cell references adjust when copied. If every copied formula must keep the same source cell, use absolute cell references, such as B2 written with dollar signs as $B$2, while preserving the sheet span.

When a result looks wrong, inspect the formula bar and compare the referenced address across several sheets. Selecting a cell in the total and using Formulas > Evaluate Formula can help trace how Excel evaluates a formula step by step. The feature is available in the Windows desktop version; ribbon availability can differ in Excel for Mac and Excel for the web. For a quick manual check, temporarily add the included sheets’ values on a scratch area and compare the result.

Frequently Asked Questions

Can Excel sum the same cell on every worksheet?
Yes. If the worksheets form a continuous span in tab order and use the same cell for the values, a SUM formula with a 3D reference can add that cell across the span. The formula includes all sheets between its two named endpoints.

Does a 3D sum include hidden sheets?
Yes. Hiding a worksheet does not remove it from a 3D reference. If a hidden sheet lies between the endpoints, its referenced values still contribute to the sum.

Will a new worksheet be included automatically?
Only if it is placed between the 3D reference’s beginning and ending sheets. A worksheet inserted outside that span is not included. Review the total whenever tabs are added, moved, or renamed.

Can I sum sheets with different layouts?
You can sum selected cells with separate references if you know where the values are, but a single 3D reference requires the same cell or range address on each sheet. If the information is arranged inconsistently, standardizing the layout or consolidating the records is usually safer.

Do these formulas work in Excel for Mac?
The SUM and 3D-reference formula syntax is the same in Excel for Mac and Excel for Microsoft 365 on Windows. Ribbon locations and some interface details can vary by platform, but you can type the formula directly into a cell in either version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.