How to Use the SUM Function and a 3D Reference in Excel

How to Create a 3D Reference in Excel

The workbook contains students’ marks in different subjects and in different exams.

Create a dataset for the quarter 1 exam to include marks in different subjects.

Create a second worksheet to include marks in the half yearly exam.

In the third worksheet, enter marks in the quarter 3 exam.

Create a new worksheet to see the total marks in Physics: select C5 and use the following formula.

=SUM('Quarter 1:Quarter 2'!C5)

Press Enter.

Drag down the Fill Handle to see the result in the rest of the cells.

You can also calculate the sum by selecting marks individually:

=SUM('Quarter 1'!C5,'Half Yearly'!C5,'Quarter 2'!C5)

Use the SUM Function and a 3D Reference in Excel

Step

Create a worksheet and enter sales details in January.

In the second worksheet include sales details in March.

Create a dataset including sales amount, region, and profit in May.

Create a dataset including the sales amount, region, and profit in July.

Open a new worksheet (Summary) to calculate the grand total sales amount and profit.
Select C4 and enter the following formula.

=SUM('Jan2022:July2022'!H4)

Press Enter.

To calculate the grand total profit, select C5.
Enter the following formula.

=SUM('Jan2022:July2022'!H5)

Press Enter.

How to Include a New Worksheet in an Existing 3D Reference in Excel

Steps

Create a dataset including the sales amount, region, and profit in April.

You will see a change of values in the summary worksheet: the grand total sales amount increases from $83381 to $106706 and the grand total profit increases from $22926 to $30854. (the 3D reference automatically calculates the new data).

Changes in the 3D Reference While Inserting, Deleting or Moving Sheets in Excel

1. Inserting a Sheet

Insert a new worksheet after the last sheet.

The grand total will be the same. You need to change the reference or move the new sheet.

2. Deleting a Sheet

If you delete a worksheet between the first and the last sheet, the 3D reference recalculate.

May 2022 was deleted: the grand total sales amount changed from $106706 to $84709, and the grand total profit changed from $30854 to $25721.

3. Changing the Sequence of Sheets

If you move a worksheet between the first and the last sheet, the 3D reference recalculate.

Sept 2022 was moved: the grand total sales amount increased from $83381 to $106706 and the grand total profit increased from $22926 to $30854.