Microsoft Office Tutorials and References

In Depth Information

**Summing Across Sheets**

Summing Across Sheets

Formulas can work with cells in other worksheets. You just need to precede the cell reference

with the sheet name and an exclamation point. For example, the following formula adds 12 to the

value in cell C1 on Sheet2:

=Sheet2!C1+12

What if you need to calculate the sum of all values in C1 on Sheet2 through Sheet6? This formula

does the job:

=SUM(Sheet2:Sheet6!C1)

In this case, the colon separates the first sheet name and the last sheet name. To create such a

formula by pointing, follow these steps:

1.
Activate the cell that will contain the formula and type
=SUM(
.

2.
Click the sheet tab for the first sheet (in this case, Sheet2) and select the cell (in this

case, C1).

3.
Press Shift and click the sheet tab for the last sheet (in this case, Sheet6).

4.
Press Enter, and the formula is entered into the cell.

In Step 2, you can select a multicell range rather than a single cell. For example, this formula

returns the sum of C1:F12 on all sheets from Sheet2 through Sheet6:

=SUM(Sheet2:Sheet6!C1:F12)

And now, I show you an interesting trick that I learned from reading the Excel newsgroups. If you

want to sum the same cell on all sheets except the active sheet, just enter a formula like this:

=SUM(‘*’!C1)

The asterisk serves as a wildcard character that’s interpreted to mean “all sheets except this

one.” When you press Enter after typing this formula, Excel converts the formula to use the

actual sheet names. It even works if the active sheet is in the middle of other sheets. For example,

if a workbook has six sheets and you enter the preceding formula in a cell on Sheet3, Excel

creates the following formula:

=SUM(Sheet1:Sheet2!C1,Sheet4:Sheet6!C1)