3D sum multiple worksheets

To sum the same range in one or more sheets, you can use the SUM formula with a special syntax called a "3d reference". In the example shown, the formula in D6 is:

=SUM(Week1:Week5!D6)

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

Sheet1:Sheet3!A1

Means: cell A1 from Sheet1 to Sheet3.

In the example shown:

=SUM(Week1:Week5!D6)

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

=SUM(Week1!D6,Week2!D6,Week3!D6,Week4!D6,Week5!D6)

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

Other examples

You can use 3D references in other formulas as well. For example:

=COUNTA(Sheet1:Sheet5!A1) // count values in A1
=AVERAGE(Sheet1:Sheet5!A1) // average values in A1