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.
This Tutorial Covers:
Sum Across Multiple Sheets in Excel – Single Cell
Below I have a dataset with quarter-wise sales for 10 stores.
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.
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).
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:
- In the cell where you want the sum value, enter
=SUM(
- Select the first worksheet (Q1 in this example)
- Hold the SHIFT key and click on the last worksheet tab name (Q4 in this example)
- Now in the active sheet, the one that’s visible, click on cell B2
- 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)
You can drag this down for all the cells in column B in the summary worksheet.
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.
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:
- In the cell where you want the sum value, enter
=SUM(
- Select the first worksheet (Q1 in this example)
- Hold the SHIFT key and click on the last worksheet tab name (Q4 in this example)
- Now in the active sheet, select B2:D2
- Hit the Enter key
This will give you the following formula:
=SUM('Q1 Sales:Q4 Sales'!B2:D2)
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:
- How to Sum Only Positive or Negative Numbers in Excel (Easy Formula)
- How to Sum a Column in Excel (5 Really Easy Ways)
- How to SUM values between two dates (using the SUMIFS formula)
- How to Get the Sheet Name in Excel?
- How to Reference Another Sheet or Workbook in Excel (with Examples)
- AutoSum in Excel (Shortcut)
