To add values from the same cell on several worksheets, use =SUM(Jan!B2,Feb!B2,Mar!B2). This example assumes the values are in B2 on sheets named Jan, Feb, and Mar. For sheets next to each other in the workbook, a 3-D reference can add the same cell or range across all of them. The steps below show how to set up either method in Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook containing the worksheets you want to total. The values can be in the same cell on each sheet or in matching ranges.
- A result cell for your total, preferably on a summary sheet. Create one if you do not already have a suitable place for the result.
- The exact worksheet names, including spaces, so you can reference the right tabs in your formula.
Step-by-Step
- Check how the source sheets are organized. Open each worksheet and identify the cells or range you want to add. For a simple total, the same location on each sheet is easiest to reference—for example, B2 on each monthly tab. If you want to total a list, make sure the matching ranges cover the same rows and columns on every sheet. Note whether the tabs are next to each other; that determines whether a 3-D reference is suitable.
- Choose or create a summary sheet. Click the worksheet tab where you want the answer to appear. To add a blank sheet, click the plus sign beside the sheet tabs, then rename the new tab by double-clicking its name and typing a clear label such as Summary. Put a description beside the result, such as Total sales, so another person can tell what the number represents. Keep the summary sheet outside any group of source tabs that you plan to include with a 3-D reference.
- Click the cell where the total should appear. Select a blank cell on the summary sheet, such as B2. You can type a formula directly into that cell or into the formula bar. Do not type the formula into a source cell if you want to preserve the original values. Press Enter after entering the formula to calculate the total.
- Add the same cell from named sheets. For a workbook with values in B2 on three sheets called Jan, Feb, and Mar, enter the formula below. This method is useful when the sheets are not next to each other, or when you want to name exactly which sheets are included.
Assumed layout: Jan!B2, Feb!B2, and Mar!B2 each contain one value; the formula goes in a separate summary cell.=SUM(Jan!B2,Feb!B2,Mar!B2)
Excel adds those three cells and displays the result. For example, if they contain 120, 85, and 95, the result is 300. To add more sheets, include another sheet reference separated by a comma inside SUM. Check that each referenced tab name matches its worksheet exactly. - Reference sheet names that contain spaces. Excel needs single quotation marks around a worksheet name that contains a space or certain special characters. With B2 holding a value on each of North Jan and North Feb, use this formula in your summary cell.
Assumed layout: North Jan!B2 and North Feb!B2 contain the values to add; the result cell is on another sheet.=SUM('North Jan'!B2,'North Feb'!B2)
The single quotes are part of the reference syntax, not part of the tab name. If you create a reference by typing an equals sign, clicking a sheet tab, selecting a cell, and returning to the summary sheet, Excel can insert the reference for you. You can then complete the formula by adding the other cell references and closing the SUM function. - Add a range from several sheets. If each monthly sheet has weekly figures in B2:B4, you can add all three matching ranges by listing each range in SUM. This is explicit: only the sheets in the formula are included.
Assumed layout: Jan!B2:B4, Feb!B2:B4, and Mar!B2:B4 each contain the figures to total; the result cell is separate.=SUM(Jan!B2:B4,Feb!B2:B4,Mar!B2:B4)
For example, if Jan contains 120, 85, and 95; Feb contains 110, 90, and 100; and Mar contains 130, 80, and 105, the result is 815. SUM ignores blank cells and adds numeric values. It does not require every range to contain the same number of numeric entries, but matching layouts make the result easier to verify. - Use a 3-D reference for sheets that sit together. If the worksheets you want are in one continuous block of tabs, a 3-D reference can add the same cell across every sheet in that block. The following formula adds B2 on every sheet from Jan through Mar, including Jan and Mar. The tabs must be ordered Jan, Feb, Mar, with no unrelated worksheet between them.
Assumed layout: Jan, Feb, and Mar are consecutive worksheet tabs, and each has the value to add in B2; the formula is entered on a sheet outside that tab block.=SUM(Jan:Mar!B2)
For the sample B2 values of 120, 85, and 95, the result is 300. A 3-D reference uses the first and last sheet names separated by a colon, followed by an exclamation mark and the cell address. It is concise when the workbook has many consistently arranged sheets, but it includes every sheet between the two endpoints. - Apply a 3-D reference to a range, if needed. You can use the same sheet span to add a matching block of cells on every included worksheet. For weekly data in B2:B4 on the consecutive Jan, Feb, and Mar tabs, enter:
Assumed layout: Jan through Mar are consecutive tabs, and B2:B4 on each contains the values to add; the formula is placed outside the block.=SUM(Jan:Mar!B2:B4)
With the sample figures from the previous range example, this also returns 815. Confirm that the first and last sheet names are correct and that no extra tab lies between them before relying on the result. - Check the total and save the workbook. Compare the result with a manual check or a small test using known values. Click the result cell and inspect the formula bar to confirm that the formula points to the intended cells and sheets. If you change a source value, Excel normally recalculates the total automatically. Save the workbook after confirming the result.
On Mac and Excel for the web, the SUM formulas and worksheet reference syntax are the same. Ribbon and sheet-tab layouts can differ slightly, but you can still select the result cell and enter the formula. In Excel for the web, if sheet tabs are not visible, use the navigation controls at the lower left of the workbook to locate them.
Common Mistakes
- Using the wrong sheet name: A misspelling or an outdated tab name can cause a reference error or leave out the intended sheet. Compare the formula with the exact tab label. For names with spaces, enclose the full name in single quotes.
- Including extra tabs in a 3-D reference: A 3-D formula adds every sheet between its start and end tabs. If an instructions or summary sheet is in the middle, its matching cell may be added too. Move the source tabs together or use a formula that lists only the intended sheets.
- Assuming one cell reference adds a whole table: A formula that refers to B2 adds B2 only. To total a range, include the range address, such as B2:B4, and make sure it is the range you mean to add.
- Forgetting quotation marks around spaced names: A tab named North Jan needs quotes in a direct formula reference. Use single quotes around the sheet name, then an exclamation mark and the cell address.
- Typing over source data: Enter the total in a designated summary cell rather than a cell holding one of the original figures. Overwriting a source value changes the data being totaled.
- Confusing a displayed number with its stored value: A cell may show a rounded amount while retaining more decimal places. SUM adds the stored values, not necessarily the rounded display. Check number formatting or the source values if a total differs by a small amount.
Tips
- Use SUM rather than typing addition signs for longer lists. SUM makes the included cells easier to scan and extend. For just two cells, a plus sign can work, but a list of many sheets is usually clearer as a SUM formula.
- Keep recurring sheets consistent. Put the same kind of figure in the same cell or range on every monthly or regional sheet. Consistent layouts make formulas simpler and reduce the chance of totaling the wrong row.
- Choose explicit references when the included sheets might change. A formula listing Jan, Feb, and Mar makes the inclusion clear. A 3-D reference is shorter, but inserting another sheet between its endpoints makes that sheet part of the total too.
- Label the source period near the answer. A heading such as Jan–Mar total helps readers understand what a formula represents. If the period changes, update the heading and verify that the formula covers the same period.
- Use Excel’s click-to-reference method to avoid typos. Start typing the formula in the summary cell, select the source sheet tab, click the desired cell or drag over the range, then add a comma before selecting another sheet. Return to the summary sheet and press Enter to finish. Excel inserts the sheet reference based on the cell you selected.
- Consider whether a formula should include future sheets. A 3-D formula can automatically include a newly inserted worksheet if it is placed between the start and end tabs. That may be helpful for a workbook designed around monthly tabs, but it can also change a total unexpectedly. Decide which behavior you want before arranging tabs.
Troubleshooting
The formula displays #REF!: Excel may not be able to find a referenced sheet or cell, often because a sheet was deleted or renamed. Check each sheet name and cell address in the formula. If a sheet was renamed, edit the reference or rebuild it by selecting the cell on the renamed sheet.
The formula displays #NAME? or does not calculate: Check for a misspelled function, missing parentheses, or a sheet name that needs single quotes. Confirm the formula begins with an equals sign and that the references use an exclamation mark before the cell address. In some regional settings, Excel uses semicolons rather than commas between function arguments; if Excel rejects a correctly written comma-separated formula, check the list separator used by your installation and replace the separators as required.
The total is zero or lower than expected: Confirm that the cells contain numbers rather than numbers stored as text, and check that the formula points to the cells with the actual data. If the source cells contain formulas, inspect their results too. A cell that looks blank may contain a zero or a formula returning an empty string; neither should be mistaken for an omitted worksheet reference.
The 3-D result is too high: Inspect every tab between the start and end sheets. Any intervening sheet is included, even if its purpose is unrelated. Use individually listed references for the intended tabs, or move the source tabs into one uninterrupted block and verify the endpoints again.
The total does not update after editing a value: Check that calculation is set to automatic: select Formulas > Calculation Options > Automatic. If the workbook is set to manual calculation, select Formulas > Calculate Now to recalculate. Also make sure the edited cell is one of the references in the formula, not just a nearby cell.
Frequently Asked Questions
Can I add cells from sheets that are not next to each other?
Yes. List each sheet and cell explicitly inside SUM. A 3-D reference is intended for a continuous block of tabs, so it is not the right choice when an unrelated sheet sits between the sheets you want to include.
Can I add the same range from several worksheets?
Yes. Either list each matching range in SUM or use a 3-D reference when all the source sheets are consecutive. For example, the range version of a 3-D reference adds the specified range on every sheet in the span. Check that each tab uses the same layout.
Do worksheet names with spaces need quotation marks?
Yes, use single quotation marks around the sheet name in a direct reference, as in ‘North Jan’!B2. Excel may add the quotes automatically when you select the cell by clicking its tab.
Can I put the result on one of the sheets being added?
Yes, if the result cell is not itself included in the reference. For a 3-D reference, placing the formula in a sheet inside the referenced span can create a circular reference if the formula cell falls within the cells being summed. Keep the result outside the summed range or put it on a separate summary sheet.
Will the formula include a new sheet I add later?
A formula listing specific sheet references will not include a new sheet until you add its reference. A 3-D reference includes sheets that fall between its endpoints, so a new tab inserted within that span can be included automatically. Check the total after changing the workbook’s sheet order.
Can I use the same formulas in Excel for Mac or the web?
Yes. The SUM function and cross-sheet reference syntax shown here work in current Excel for Mac and Excel for the web. The controls for finding or arranging tabs may look different, but you can enter the formulas directly into a cell.
Related
- Export Excel to Word: Methods and Formatting Tips
- Expand Data Validation Lists in Excel
- Excel Zoom Shortcut: Fast Ways to Change Magnification
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Custom Cover Page in Word: Create and Add Your OwnTo add a custom cover page in Word, place a new page at the start of your document,…
- How to Split Names in Excel Into Two CellsTo split names in Excel into two cells, select the name column and choose Data > Text to…
- Excel Shortcut Keys: Essential Commands for WindowsFor Excel for Microsoft 365 on Windows, the most useful shortcut keys include Ctrl+C and Ctrl+V to copy…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, select Add…
- Excel Consolidate Ranges: Methods, Steps, and LimitsTo consolidate ranges in Excel, use Data > Consolidate to combine values with a function such as Sum,…
- Excel Mac: Distinct Count in a PivotTableExcel for Mac does not offer Distinct Count as a summary option in a standard PivotTable. On a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.