How to Sum Across Multiple Sheets in Excel? (3D SUM Formula)

If you need to get the sum across multiple worksheets, you can use one of the less-known Excel feature called 3D referencing.

With 3D referencing, you can refer to the same cell in multiple worksheets, and can also use this in formulas such as the SUM or AVERAGE, or COUNT.

In this short tutorial, I will show you how to quickly sum across multiple worksheets using this 3D reference feature.

Sum Across Multiple Sheets in Excel – Single Cell

Below I have a dataset with quarter-wise sales for 10 stores.

Quarter1 Sales Data

In the screenshot above, you can see the sales data for Quarter 1 in the sheet named Q1, and I have similar data for the other three quarters (Q2, Q3, and Q4) in three separate worksheets.

Other quarter sheets

And I want to get the sum of each store from all four quarters and get it in the Summary tab (where I have a table as shown below).

Summary Sheet

Now if I do this the regular way, I would first have to enter the equal to sign in the Summary sheet (in cell B2, then go to each worksheet, then select cell B2 in that worksheet, then add a plus sign, and then do the same for all the other worksheets.

This will give me a formula as shown below:

='Q1 Sales'!B2+'Q2 Sales'!B2+'Q3 Sales'!B2+'Q4 Sales'!B2

While this works, this is inefficient and error-prone.

Also, note that I only have 4 worksheets in this case, but in case you have many more (say 12 worksheets for each month), then doing this would take even more time.

Let me show you a better method to do this.

Below are the steps to get the sum across multiple worksheets using 3D referencing:

  1. In the cell where you want the sum value, enter
=SUM(
Enter SUM formula in summary sheet
  1. Select the first worksheet (Q1 in this example)
  2. Hold the SHIFT key and click on the last worksheet tab name (Q4 in this example)
  3. Now in the active sheet, the one that’s visible, click on cell B2
Select cell B2
  1. Hit the Enter key

The above steps would give you the below formula in cell B2 in the Summary sheet:

=SUM('Q1 Sales:Q4 Sales'!B2)
3D Sum value in summary sheet

You can drag this down for all the cells in column B in the summary worksheet.

Drag the formula to sum across worksheets

In the above formula, when you followed the steps I’ve mentioned above, it automatically created a 3D reference – ‘Q1 Sales:Q4 Sales’!B2

This reference refers to all the B2 cells in the sheets between Q1 Sales and Q4 Sales.

As you can see, this 3D formula is shorter and a lot easier to manage than going to each worksheet and selecting the cell that you want to add.

Note: If you enter the same formula manually in a cell, you would still get the same result.

Sum Across Multiple Sheets in Excel – Range of Cells

In the above example, I showed you how to get the sum across multiple sheets when I only wanted to add one cell from each worksheet.

But what if I want to add a range of cells from each worksheet and then get the result in the Summary sheet?

For example, below I have the sales data of multiple products in the Q1 sheet, and I have a similar construct across worksheets or other Quarters as well.

Sales data for multiple products

Now, I want to get the sum of all the products in each quarter in the summary sheet.

Below are the steps to do this:

  1. In the cell where you want the sum value, enter
=SUM(
  1. Select the first worksheet (Q1 in this example)
  2. Hold the SHIFT key and click on the last worksheet tab name (Q4 in this example)
  3. Now in the active sheet, select B2:D2
  4. Hit the Enter key

This will give you the following formula:

=SUM('Q1 Sales:Q4 Sales'!B2:D2)
Fomrula to sum range of cells across multiple sheets

As you can see, instead of adding one cell from each sheet, we have used a formula to add three cells across four different worksheets.

So this is how you can easily get the sum of values across multiple worksheets using the 3D reference formula.

You can also use the same method with other formulas such as COUNT or AVERAGE.

I hope you found this Excel tutorial useful.

Other Excel tutorials you may also like:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster