the sum of cells of many sheets

[Jeff Klein]

I have approx 150 sheets named with my customers name. I want to create a
cell that shows the sum of C12 in each sheet. Each of my clients has a
number in C12 that shows the "Hours Required". I am trying to obtain a
total of all hours for all clients.

='Doe, John'!C12+'Smith, Jane'!C12 Works fine for a few sheets but what if
I have 150 sheets? What is the formula?
I have also tried =SUM('Doe, John'!C12:'Smith, Jane'!C12) This gives me
"$VALUE!"

 

[Doug Glancy]

Jeff,

Here's how it would look for summing A1 in Sheet1 through Sheet3:

=SUM(Sheet1:Sheet3!A1)

From 2003 Help here's some instructions:
1.. Click the cell where you want to enter the function.
2.. Type = (equal sign), enter the name of the function, and then type an
opening parenthesis.
3.. Click the tab for the first worksheet to be referenced.
4.. Hold down SHIFT and click the tab for the last worksheet to be
referenced.
5.. Select the cell or range of cells to be referenced.
6.. Complete the formula, and press ENTER.
hth,

Doug Glancy

 

[James Cox]

Jeff -

From the Excel help item titled "About cell and range references"

The 3-D reference style

If you want to analyze data in the same cell or range of cells on multiple
worksheets within the workbook, use a 3-D reference. A 3-D reference
includes the cell or range reference, preceded by a range of worksheet
names. Excel uses any worksheets stored between the starting and ending
names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the
values contained in cell B5 on all the worksheets between and including
Sheet 2 and Sheet 13.

a.. You can use 3-D references to refer to cells on other sheets, to
define names, and to create formulas by using the following functions: SUM,
AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV,
STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.
b.. 3-D references cannot be used in array formulas.
c.. 3-D references cannot be used with the intersection operator (a single
space) or in formulas that use implicit intersection.
How 3-D references change when you move, copy, insert, or delete worksheets

The following examples explain what happens when you move, copy, insert, or
delete worksheets that are included in a 3-D reference. The examples use the
formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2
through 6.

Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the
endpoints in this example), Microsoft Excel includes all values in cells A2
through A5 from the added sheets in the calculations.

Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their
values from the calculation.

Move If you move sheets from between Sheet2 and Sheet6 to a location
outside the referenced sheet range, Excel removes their values from the
calculation.

Move an endpoint If you move Sheet2 or Sheet6 to another location in the
same workbook, Excel adjusts the calculation to accommodate the new range of
sheets between them.

Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the
calculation to accommodate the range of sheets between them.

I'm not sure if the above works with renamed worksheets, i.e. Smith, Jane
instead of Sheet23, but I suspect it will follow the order that the
worksheets are displayed in the workbook...

HTH,

James Cox