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
Is there a quick formula I can use to add all A1 cells across 100 different
workbooks?
Kind regards,
Neil
D
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)
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)
J
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...
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...
P
Per Jessen
Hi Neil
=SUM(Sheet1:Sheet100!A1)
Best regards,
Per
=SUM(Sheet1:Sheet100!A1)
Best regards,
Per
D
David Biddulph
If the OP meant what he said, he was asking for a sum across workbooks, not
across worksheets in one workbook.
across worksheets in one workbook.
D
David Biddulph
Workbooks, not worksheets?
--
David Biddulph
--
David Biddulph
Per Jessen said:
N
Neil Pearce
Sorry I meant worksheets across workbook.
David Biddulph said:
N
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
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
D
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"))
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"))
P
Per Jessen
David, I guess you are right.
Obviously I didn't read OP's question carefully enough...
Obviously I didn't read OP's question carefully enough...
N
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 D21
70, 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?
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
70, 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?