Summing Across Workbooks

  • Thread starter Thread starter Neil Pearce
  • Start date Start date
N

Neil Pearce

Dear all,

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


Kind regards,

Neil
 
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)
 
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...
 
If the OP meant what he said, he was asking for a sum across workbooks, not
across worksheets in one workbook.
 
Sorry I meant worksheets across workbook.

David Biddulph said:
If the OP meant what he said, he was asking for a sum across workbooks, not
across worksheets in one workbook.
Click to expand...
 
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
 
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"))
 
David, I guess you are right.

Obviously I didn't read OP's question carefully enough...
 
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 D21:D70, 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?
 
Back
Top