Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

3D sum multiple worksheets in Excel

This tutorial shows how to 3D sum multiple worksheets in Excel using the example below;




To sum the same range in one or more sheets, you can use the SUM formula with a special syntax called a “3d reference”.

Worked Example:   Average last 5 values in columns in Excel

In the example shown, the formula in D6 is:


How this formula works

The syntax for referencing a range of sheets is a built-in feature and works a bit like a reference to a range of cells. For example


Means: cell A1 from Sheet1 to Sheet3.

Worked Example:   Calculate Weighted Average in Excel

In the example shown:


Will sum cell D6 from Week1 to Week5, equivalent to:


In this workbook, the sheets Week1 through Week 6 all look exactly like this:

Worked Example:   How to calculate average last N values in a table in Excel

Other examples

You can use 3D references in other formulas as well. For example, the formula in E6 averages D6 like this:


Leave a Reply

Your email address will not be published. Required fields are marked *