Dear all,

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


Kind regards,

Neil

Try the below to sum from Sheet1 to Sheet100

=SUM(Sheet1:Sheet100!A1)

If this post helps click Yes
---------------
Jacob Skaria


"Neil Pearce" wrote:

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)



Neil Pearce wrote:

Dear all,

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

Kind regards,

Neil

--

Dave Peterson

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...
--
HTH...

Jim Thomlinson


"Neil Pearce" wrote:

Dear all,

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


Kind regards,

Neil

Hi Neil

=SUM(Sheet1:Sheet100!A1)

Best regards,
Per

"Neil Pearce" skrev i meddelelsen
...
Dear all,

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


Kind regards,

Neil

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

"Jacob Skaria" wrote in message
...
Try the below to sum from Sheet1 to Sheet100

=SUM(Sheet1:Sheet100!A1)

If this post helps click Yes
---------------
Jacob Skaria


"Neil Pearce" wrote:

Dear all,

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


Kind regards,

Neil

Workbooks, not worksheets?
--
David Biddulph

"Per Jessen" wrote in message
...
Hi Neil

=SUM(Sheet1:Sheet100!A1)

Best regards,
Per

"Neil Pearce" skrev i meddelelsen
...
Dear all,

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


Kind regards,

Neil

Sorry I meant worksheets across workbook.

"David Biddulph" wrote:

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

"Jacob Skaria" wrote in message
...
Try the below to sum from Sheet1 to Sheet100

=SUM(Sheet1:Sheet100!A1)

If this post helps click Yes
---------------
Jacob Skaria


"Neil Pearce" wrote:

Dear all,

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


Kind regards,

Neil

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,Sta rt:End!$B$1:$B$10)

Any ideas would be much appreciated.


Kind regards,

Neil


"Dave Peterson" wrote:

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)



Neil Pearce wrote:

Dear all,

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

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" ))



Neil Pearce wrote:

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,Sta rt:End!$B$1:$B$10)

Any ideas would be much appreciated.

Kind regards,

Neil

"Dave Peterson" wrote:

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)



Neil Pearce wrote:

Dear all,

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

Kind regards,

Neil

--

Dave Peterson


--

Dave Peterson

David, I guess you are right.

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

"David Biddulph" <groups [at] biddulph.org.uk skrev i meddelelsen
...
Workbooks, not worksheets?
--
David Biddulph

"Per Jessen" wrote in message
...
Hi Neil

=SUM(Sheet1:Sheet100!A1)

Best regards,
Per

"Neil Pearce" skrev i meddelelsen
...
Dear all,

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


Kind regards,

Neil

As it happens, the OP has said in another branch of this thread that he did
intend to say worksheets, so you were right all along.
--
David Biddulph

Per Jessen wrote:
David, I guess you are right.

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

"David Biddulph" <groups [at] biddulph.org.uk skrev i meddelelsen
...
Workbooks, not worksheets?
--
David Biddulph

"Per Jessen" wrote in message
...
Hi Neil

=SUM(Sheet1:Sheet100!A1)

Best regards,
Per

"Neil Pearce" skrev i meddelelsen
...
Dear all,

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


Kind regards,

Neil

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?

"Dave Peterson" wrote:

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" ))



Neil Pearce wrote:

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,Sta rt:End!$B$1:$B$10)

Any ideas would be much appreciated.

Kind regards,

Neil

"Dave Peterson" wrote:

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)



Neil Pearce wrote:

Dear all,

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

Kind regards,

Neil

--

Dave Peterson


--

Dave Peterson