Summing Across Sheets<= /span>

This article is an excerpt from John Walkenbach’s Favorite Excel 2007 Tips & Tricks. A.k.a. Mr. Spreadsheet, John is arguably the foremost authority on Excel. His forty-plus books include E= xcel 2007 Power Programming with VBA, Excel 2007 Formulas, and the bestsellin= g Excel 2007 Bible, all published by Wiley. He has also written hundreds of arti= cles and created the award-winning Power Utility Pack. To buy this book, visi= t Wiley.com.

Formulas can work with cells in other worksheets. You just n= eed to precede the cell reference with the sheet name and an exclamation point. F= or example, the following formula adds 12 to the value in cell C1 on Sheet2:<= span style=3D'font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:"Times New= Roman","serif"; mso-fareast-font-family:"Times New Roman";color:windowtext'>

=3DSheet2!C1+12

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

=3DSUM(Sheet2:Sheet6!C1)

In this case, the colon separates the first sheet name and t= he last sheet name. To create such a formula by pointing, follow these steps:

1. Activate the cell that will contain the formula, and type =3DSUM(.

2. Click the sheet tab for the first sheet (in this case, Sheet2), and select the c= ell (in this case, C1).

3. Press Shift, and click the sheet tab for the last sheet (in this case, Sheet6).<= /p>

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

In Step 2, you can select a multicell range rather than a si= ngle cell. For example, this formula returns the sum of C1:F12 on all sheets fr= om Sheet2 through Sheet6:

=3DSUM(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 shee= ts except the active sheet, just enter a formula like this:

=3DSUM('*'!C1)

The asterisk serves as a wildcard character that's interpret= ed to mean "all sheets except this one." When you press Enter after ty= ping 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, i= f a workbook has six sheets and you enter the preceding formula in a cell on Sheet3, Excel creates the following formula:

=3DSUM(Sheet1:Sheet2!C1,Sheet4:Sheet6!C1)

It gets even better. Enter the following formula to find the= sum of cell C1 on all sheets that begin with the characters Region:

=3DSUM('Region*'!C1)

Excel might convert this formula to something like this:

=3DSUM(Region1:Region4!C1)

You can also use the ? wildcard character, which indicates a= ny single character. For example, when you enter the formula that follows, Excel cre= ates a formula that sums the value only on Sheet1 through Sheet9 (sheet names t= hat contain a single numeric digit):

=3DSUM('Sheet?'!C1)

This trick isn’t limited to the SUM function. It works= with other functions, such as AVERAGE, MIN, and MAX.

Comments or suggestions = for this article? Please send us your feedback!

<= o:p>