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)

Search JabSto ::

Custom Search