Summing Across Workbooks

[Neil Pearce]

Dear all,

Is there a quick formula I can use to add all A1 cells across 100 different
workbooks?


Kind regards,

Neil

 

[Dave Peterson]

Not that I know.

In fact, depending on the version of excel that you're using, you may not be
able to do it in a single formula. The length of the formula is limited to 1024
characters (when measured in R1C1 reference style) in xl2003 and below.

If you meant worksheets within the same workbook, you could use a "sandwich"
technique:

Put a sheet to the left of the 100 sheets. Name it Start.

Put a sheet to the right of the 100 sheets. Name it End.

Then make sure that there are no other sheets between those Start and End
sheets.

Then put this in a cell on a different sheet (outside that sandwich, too!):

=sum(start:finish!A1)

 

[Jim Thomlinson]

Workbooks or worksheets? Worksheets is possible.
Type =Sum(
Highlight the desired worksheets and select cell A1
Type your closing brackets and you are done with something like...
=SUM(Sheet1:Sheet100!A1)

If you mean workbooks then not there is nothing simple and even if you could
it would be an incredibly brittle formula. If one of the workbooks has
something wrong in A1 then the formula colapses. Worse still if one of the
workbooks gets moved or deleted then the entire formula colapses. If the
formula colapses there is no effective way to debug it especially since it
would be incredibly long...

 

[Per Jessen]

Hi Neil

=SUM(Sheet1:Sheet100!A1)

Best regards,
Per

 

[David Biddulph]

If the OP meant what he said, he was asking for a sum across workbooks, not
across worksheets in one workbook.

 

[David Biddulph]

Workbooks, not worksheets?
--
David Biddulph

Hi Neil

=SUM(Sheet1:Sheet100!A1)

Best regards,
Per

 

[Neil Pearce]

Sorry I meant worksheets across workbook.

If the OP meant what he said, he was asking for a sum across workbooks, not
across worksheets in one workbook.

 

[Neil Pearce]

Thanking-you all for the earlier repsonses.

Is it possible to apply a SUMIF formula across several worksheets within a
workbook too? The below appeared not to work

=SUMIF(Start:End!$A$1:$A$10,Summary!$A$1:$A$10,Start:End!$B$1:$B$10)

Any ideas would be much appreciated.


Kind regards,

Neil

 

[Dave Peterson]

Biff (T. Valko) posted this response to a similar question:

Create a list of your sheet names and give that list a defined name. Say
that name is Snames.

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1"),">0"))

 

[Per Jessen]

David, I guess you are right.

Obviously I didn't read OP's question carefully enough...

 

[Neil Pearce]

Having searched and read similar queries there was a reference to the
website: http://www.mcgimpsey.com/excel/threedsumif.html

Adapting and (with great originality) naming my worksheets to sum
"worksheetsTOsum", the following formula works great.

=SUMPRODUCT(SUMIF(INDIRECT("'"&worksheetsTOsum&"'!A21:A70"),$A12,INDIRECT("'"&worksheetsTOsum&"'!C21:C70")))

This is great. However I'm required to use this formula to sum several
individual columns, i.e. the D2170, E21:E70, F21:F70...

As Indirect is utilised in the above copying and pasting the formula does
not alter these cells' references. Is there a way to amend the formula such
that the column references are automatically changed when copying the formula?