Sum across multiple worksheets

In this example, the goal is to sum total points for each student across five worksheets that all have the same structure. This can be accomplished with a 3D reference, as explained below.

Standard reference

Before we look at how 3D references work, let's look at the standard approach. To solve this problem with a standard cell reference, you can input each reference manually like this:

=Week1!D6+Week2!D6+Week3!D6+Week4!D6+Week5!D6

Alternately, you can provide the five references above to the SUM function like this:

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

Both formulas return a correct result. However, the formulas are unwieldy and will become more complex as the number of sheets increases. A better approach is a 3D reference.

3D reference

A 3D reference is a special kind of reference that includes more than one worksheet, a bit like a range refers to more than one cell. To target three worksheets like Sheet1, Sheet2 and Sheet3, a 3D reference starts like this

Sheet1:Sheet3

Note that a colon (:) separates the first worksheet from the last worksheet. To refer to cell A1 in these worksheets, add an exclamation mark (!) and the cell reference like this:

Sheet1:Sheet3!A1

The formula above is the equivalent of three separate references:

Sheet1!A1
Sheet2!A1
Sheet3!A1

Sum sheets

In the example shown, we want to sum total points for each person across five worksheets, Week1 through Week5. In the Summary sheet, the formula in cell D5, copied down, is:

=SUM(Week1:Week5!D5)

This formula is equivalent to the following long-hand formula:

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

Note: when using 3D references, it is important that all sheets have exactly the same structure. In the example shown, the five sheets being referenced all have the same layout as Week1 below:

Average sheets

You can use a 3D reference with other functions as well. In the example shown, the formula to average scores in cell E5 is:

=AVERAGE(Week1:Week5!D5)

The result in cell E5 is 86.8, the average of cell D5 in the five worksheets being referenced.

Other examples

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

=COUNTA(Sheet1:Sheet5!A1) // count values
=MIN(Sheet1:Sheet5!A1) // minimum value
=MAX(Sheet1:Sheet5!A1) // maximum value

Not all functions support 3D references. For example COUNTIFS, SUMIFS and other functions in this group do not work with 3D references.