Summing rows accross sheets

[Mark A. Sam]

Hello,

I have a spreadsheet with three similiar sheets. I need to sum the rows
from sheet one through sheet three but don't know how to reference the
sheets.

Thank you for any help and God Bless,

Mark A. Sam

 

[Harald Staff]

Hi Mark

=SUM(Sheet1:Sheet3!A1:A12)

HTH. Best wishes Harald

 

[Mark A. Sam]

Thank you Harold.

Hi Mark

=SUM(Sheet1:Sheet3!A1:A12)

HTH. Best wishes Harald

 

[Mark A. Sam]

Harold,

I think my description was bad. What you gave me seemed to be to add all the
columns and rows. I wanted the sum of a single row across three worksheets.
I reworked it to this:

=SUM(Sheet1!B7:R7) + SUM(Sheet2!B7:R7) + SUM(Sheet3!B7:R7)

Now I can copy and paste the formula to each row. Thanks again for your
help. It got me what I needed.

 

[Roger Govier]

Hi Mark

All you needed to do was change Harald's range from going down a Column, to
going across a Row
=SUM(Sheet1:Sheet3!B7:R7)

 

[Mark A. Sam]

Thank you Roger. I don't understand the syntax. I'll adjust mine for
simplification.

 

[Roger Govier]

Hi Mark

Sum(
Sheet1:Sheet3! means include all cells within the range of Sheets Sheet1
to Sheet3 inclusive
B7:R7 all cells within the range B7 to R7 inclusive
)

Sometimes people create 2 new blank sheets and rename these sheets First and
Last.
Dragging theses sheets to any positions in the workbook to "sandwich" the
sheets they want to sum, but leaving the Summary Sheet outside the
"Sandwich" gives rise to the universal formula

=SUM(First:Last!B7:R7)

There could be 1 sheet between First and last or say 50 Sheets between First
and Last, and the formula would add all of the cells that exists in the
range defined on all of those sheets.

 

[Mark A. Sam]

Roger,

Thank you for that advice. I'm going to copy it all and save it in a text
file for future reference. I don't do an awful lot with spreadsheets, but
have a user that insists on it rather than having me do this stuff in
Access.

I went back to my initial method:

=SUM(Sheet1!B7:R7) + SUM(Sheet2!B7:R7) + SUM(Sheet3!B7:R7)

becuase I realized that sheet three used a couple extra columns and there
are columns that aren't included in the Sum() function. Also the other two
sheets may have more columns added or subtracted, so I'll just adjust the
forumula for that in the future.

God Bless,

Mark