How to Add Totals from Different Sheets in Excel

To add totals from different sheets in Excel, go to the cell where you want the grand total, type =, click the total on the first sheet, type +, click the total on the next sheet, and so on, then press Enter. If every sheet has the same layout, a 3D formula is faster: =SUM(Jan:Dec!B10) adds cell B10 on every sheet from Jan to Dec. For sheets with different layouts, use Data > Consolidate.

Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows and Mac, and Excel for the web. The VSTACK method needs Excel for Microsoft 365, Excel 2024 or Excel for the web. Last reviewed October 7, 2026.

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.

1. Click the cell where you want the grand total, for example on a Summary sheet.
2. Type =.
3. Click the first sheet’s tab, click its total cell, then type +.
4. Click the next sheet’s tab and its total cell. Repeat for each sheet.
5. 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.

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.

1. 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.
2. On the Summary sheet, type =SUM(.
3. Click the first sheet’s tab, hold Shift and click the last sheet’s tab.
4. 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:

1. Add two blank sheets named Start and End.
2. Put all your monthly sheets between them.
3. 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).

Method 4: Use Consolidate for sheets with different layouts

If each sheet lists items in a different order, Consolidate matches them by label.

1. On the Summary sheet, click the top-left cell where you want the result.
2. Select Data > Consolidate.
3. Set Function to Sum.
4. Click in Reference, go to the first sheet, select its data including the labels, and select Add. Repeat for each sheet.
5. Tick Top row and Left column under Use labels in.
6. 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. To bring the sheet into your workbook instead, see how to copy a sheet to another workbook in Excel.

Troubleshooting

#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.

Frequently asked questions

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. See how to reference a tab name in Excel.

Do these formulas update automatically?

Yes. Change a number on any sheet and the total updates. Consolidate updates only if you ticked Create links to source data.

Related: How to insert a hyperlink in an Excel cell with other text.

Related: How to compare two sheets in Excel for matches.