How to use =sumallsheets(b1)?

[Guest]

I need to total sums of different worksheets that are copied into a workbook
daily. All worksheets are identical except for the values they contain. I
have the templete and the =sumallsheets formula would be perfect but when I
entered this in my excel 2002 spreadsheet only #NAME? appears. Please help?
Chris

 

[Chip Pearson]

I'm not sure what "sumallsheets" function you're refering to, it is not a
built in worksheet function, but you can use a formula like

=SUM(Sheet1:Sheet3!A1)

to sum all the values in A1 on sheet1 through sheet3.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Pete_UK]

Well, "sumallsheets" is not a standard function recognised by Excel,
which is why you get the #NAME? error.

Imagine the sheet you want this formula to appear on is called
"Summary" and that it is the first (i.e. left-most) tab visible at the
bottom of the sheets. Insert a new (blank) sheet named "first"
immediately after the Summary sheet and before any of your other
sheets. Add another new worksheet and position it as the right-most
sheet tab and name it as "last". Essentially, you now have a
"sandwich" of sheets contained between the outer sheets "first" and
"last", and the Summary sheet does not form part of this "sandwich".

You can then enter this formula in A1 of the Summary sheet:

=SUM(first:last!A1)

and this will add up all values from cell A1 of all the sheets between
(and including) the first sheet and the last sheet. You can then copy
this formula to any other cells of the Summary sheet wherever you need
to sum the values for that cell across all of the other sheets.

Hope this helps.

Pete