Last updated on November 17th, 2026 at 03:15 pm
For a grand total across sheets in Excel, type = in the total cell, click each sheet’s total with + between them, and press Enter. When every sheet shares a layout, =SUM(Jan:Dec!B10) adds B10 on every sheet from Jan to Dec. Different layouts call for Data > Consolidate. Everything works on Windows, Mac and the web, though VSTACK needs Excel for Microsoft 365, Excel 2024 or Excel for the web.

Method 1: Click to add totals from each sheet
Best for a few sheets, or when the totals are in different cells on each sheet.
- Click the cell where you want the grand total, for example on a Summary sheet.
- Type
=. - Click the first sheet’s tab, click its total cell, then type
+. - Click the next sheet’s tab and its total cell. Repeat for each sheet.
- Press Enter. Excel returns to the Summary sheet and shows the result.
The formula looks like =Jan!B10+Feb!B12+Mar!B9. Sheet names with spaces or special characters get single quotation marks, such as ='Jan 2026'!B10.
Can I use the sheet name in a formula from a cell?
Yes, with INDIRECT, for example =INDIRECT("'"&A2&"'!B10") where A2 holds a sheet name. More examples are in referencing a tab name in Excel.
Method 2: Use a 3D SUM for sheets with the same layout
When each sheet has its total in the same cell, one short formula adds them all.
- Make sure the sheets you want to add are next to each other, and that the total is in the same cell on each, for example B10.
- On the Summary sheet, type
=SUM(. - Click the first sheet’s tab, hold Shift and click the last sheet’s tab.
- Click cell B10, type
)and press Enter.
The formula reads =SUM(Jan:Dec!B10). You can also add a whole range on every sheet, such as =SUM(Jan:Dec!B2:B9).
Make the 3D formula pick up new sheets automatically
A 3D formula includes every sheet between the first and last one named in it. To make adding sheets easy:
- Add two blank sheets named Start and End.
- Put all your monthly sheets between them.
- Use
=SUM(Start:End!B10).
Any sheet you drag between Start and End is added to the total, and any sheet you move outside them is left out.
3D references also work with AVERAGE, COUNT, MAX and MIN, but not with SUMIF, SUMIFS or COUNTIFS.
Method 3: Add totals that match a condition
To total only some rows across sheets, for example sales of one product, stack the sheets’ data with VSTACK and filter it. With product names in column A and amounts in column B on each sheet:
=SUM(FILTER(VSTACK(Jan:Dec!B2:B100), VSTACK(Jan:Dec!A2:A100)="Apples", 0))
Replace “Apples” with a cell reference, such as A2, to make a summary table for every product.
In older versions without VSTACK, add a SUMIF for each sheet: =SUMIF(Jan!A:A,"Apples",Jan!B:B)+SUMIF(Feb!A:A,"Apples",Feb!B:B). Each of those per-sheet formulas follows the pattern shown in how to use SUMIF in Excel, including wildcard and cell-reference conditions.
Method 4: Use Consolidate for sheets with different layouts
If each sheet lists items in a different order, Consolidate matches them by label.
- On the Summary sheet, click the top-left cell where you want the result.
- Select Data > Consolidate.
- Set Function to Sum.
- Click in Reference, go to the first sheet, select its data including the labels, and select Add. Repeat for each sheet.
- Tick Top row and Left column under Use labels in.
- Tick Create links to source data if you want the totals to update when the sheets change, then select OK.
Add totals from another workbook
With both workbooks open, type = in your workbook, switch to the other workbook with View > Switch Windows, click the total and press Enter. Excel writes a reference such as =[Sales.xlsx]Jan!$B$10. Keep the other file in the same place, or the link breaks. You can avoid the link altogether by copying the sheet into your workbook.
#REF! or a 3D total that’s off
#REF! error
A sheet or cell the formula points to was deleted. Re-enter the reference. Renaming a sheet doesn’t cause this, because Excel updates formulas automatically.
The 3D total is too high or too low
A sheet was moved inside or outside the range of sheets. Check the order of the tabs between the first and last sheet in the formula.
The total shows 0 or ignores some numbers
Some numbers are stored as text. Select them, select the warning icon and choose Convert to Number.
Related guides

Matt Jacobs writes and maintains the guides on Support Your Tech, which he started in 2017. He spent most of his career keeping small-business PCs running and still takes the occasional consulting job, so the steps here come from fixing the same problems on other people’s computers. Each guide follows Microsoft, Apple and Google’s own documentation and is updated when the steps change. Screenshots marked “Recreated” are illustrations of the real screens.