Exceljet

Quick, clean, and to the point

Sum across multiple worksheets

Excel formula: Sum across multiple worksheets
Generic formula 
=SUM(Sheet1:Sheet3!A1)
Summary 

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

=SUM(Week1:Week5!D5)

The result in the sum of values in cell D5 in all five worksheets.

Explanation 

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 a 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:

All sheets have the same structure

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.