How do you use the SUM function with 3D references in Excel?

We all want to save time. And, using 3D Reference in Excel can save a lot of time.

It is like a three-dimensional chart or image with more than one phase. A normal range is all about a group of cells from a single worksheet.

For Example, in =SUM(Sheet1!A1:A10), “A1:A10” is a group of cells that is referred from Sheet1.

But, a 3D reference is a range of cells in which you can refer to the same cells from multiple worksheets using a single reference. In simple words, refer to the same cell or range from multiple sheets.

Create a 3D Reference

Only one thing which you have to take care of before using a 3D reference is all the worksheets should be in a sequence. Let me show you an example.

Now here you have 5 worksheets in a workbook. So, if you want to calculate a sum of the range C5:D6 from all the 5 worksheets you have to use a formula like the below.

=(‘2009‘!A1:A10,’2010‘!A1:A10,’2011‘!A1:A10,’2012‘!A1:A10,’2013‘!A1:A10)

But if you want to create a 3D formula with a 3D reference then it will be something like this.

=SUM(‘2009:2013’!A1:A10)

How 3D Reference Works in Excel

A 3D range formula always works in two different parts.

• First: a range of worksheets: Like a range of cells, you have to create a range of worksheets from which you want to refer cells. The range of worksheets must be a continuous range. In the above example, I have 5 worksheets from 2009 to 2013.
• Second: A range of cells. A normal range of cells that you want to refer to in all the worksheets.

Adding a New Worksheet

Suppose you insert a worksheet between 2009 to 2013. It will automatically include the value of the range A1:A10 from the new sheet in the function you are using.

Deleting a Worksheet:

If you delete a worksheet from 2009 to 2013. It will automatically exclude the value of cell C4 from the function you are using.

The sequence of Worksheets:

Here in this example, the range of worksheets starts from “Sheet 2009” and ends at “Sheet 2013”.

The point is, if you move any sheet out of this range, that sheet will exclude from the formula calculation.

Conclusion

One of the most important benefits of 3D references is that they can shorten your complex formulas. You don’t have to refer to all the worksheets separately in formulas. And, I hope this method will help you write better formulas.

Now tell me one thing. Have you tried it before? Please share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share with your friends.

To sum a range of numbers is easy for most of Excel users, but do you know how to create 3D reference to sum the same range of multiple sheets as below screenshot was shown? In this article, I will introduce the steps on this job.

Create 3D reference to sum same range across sheets

List the same cell across worksheets

Create 3D reference to sum same range across sheets

To create 3D reference to sum same range across sheet, it’s very easy.

Select a cell which you will place the result at, type this formula =SUM(Jan:Dec!B1), Jan and Dec are the first and last sheets you need to sum across, B1 is the same cell you sum, press Enter key.

Tip:

1. You also can sum the same range across sheets by changing the cell reference to a range reference =SUM(Jan:Dec!B1:B3).

2. You also can use named range to sum same range across sheets as below:

Click Formulas > Define Name to enable the New Name dialog.

In the New Name dialog, give the range a name, type this =‘Jan:Dec’!B1 into the Refers to textbox, and click OK.

In a blank cell, type this formula =SUM(SumTotal) (SUMTotal is the specified named range you defined in the last step) to get the result.

List the same cell across worksheets

If you want to list the same cell across sheets in a workbook, you can apply Kutools for Excel’s Dynamically Refer to Worksheets.

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

1. In a new sheet, select B1, which cell you want to extract from each sheet, click Kutools > More > Dynamically Refer to Worksheets.

2. In the Fill Worksheets References dialog, select one order you want to fill the cells, click 

3. Click Fill Range and close the dialog. Now the value in the Cell B1 of each sheet has been listed in the active sheet, then you can do calculations as you need.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Comments (0)

No ratings yet. Be the first to rate!