MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing Across Worksheets


Posted by Gavagan on July 25, 2001 6:49 AM

Within a workbook with 60 sheets, I am looking to sum the same cell from every sheet, for example: summing every a100 cell in the workbook. Is there a way to do this: with a function(this would be easiest), or with VBA?


Posted by Art on July 25, 2001 6:53 AM

Use the consolidation function. No problem!

Posted by Mark W. on July 25, 2001 6:54 AM

See the Excel Help topic for "Refer to the same
cell or range on multiple sheets by using a 3-D
reference".

Posted by Ian on July 25, 2001 6:59 AM

Yes, first put in your =sum(
then goto the first sheet and click on the cell you want.
then with the shift key pressed highlight all the sheets you need, then hey presto you should have someting like.

=SUM(Sheet2:Sheet60!C10)

BE WARNED!! this only work with consecutive sheets (no gaps) as the Control key can't be used instead

you can use a comma
=SUM(Sheet2:Sheet3!D10,Sheet5:Sheet60!D10)

but it does give you same problem of long formulas

Ian