Gathering totals from various worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to gather cells from different worksheets and sum them on a separate
sheet. How do you do this? Thanks
 
Please provide some more information. Do you want to add the same cells from
every sheet? or you want to add different cells?
 
Thank you. I have several worksheets with totals on them and I want to
gather some of those totals and total them on one seperate worksheet.
Example: total on worksheet one is 5; total on worksheet two is 10; total on
worksheet three is 20. I want to have the sum of all those totals on
worksheet 4.
 
Assuming you have these values in cell A1 on all the three sheets and you
want the sum of these sheets in cell A1 of the sheet 4.

one way is to put this formula in Sheet4, Cell A1.

=SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1)

another way is to insert 2 new sheets. one before Sheet1 and one after
Sheet3. Name them as Start and End. then in Sheet4, enter this formula.

=SUM(Start:End!A1)

You can hide these 2 dummy sheets then.

if the cells are different on every sheet, you can just use the first
formula and change the cell references as needed.

Thanks
 
If you're summing the same cell from multiple worksheets in the same workbook:
Select the cell where you want the total to appear, click AutoSum, click on
the first sheet in the range of sheets, hold down the shift key and click on
the last sheet in the range of sheets and then click the cell you want to
total from all sheets. Hit the enter key (or click the enter button on the
formla bar) to complete.
If you were totaling cell B25 from sheets named Jan thru Dec, it would look
like this:
=SUM(Jan:Dec!B25)
If the cells are not in the same location on each sheet, select the cell
where you want the total to appear, type an equal sign, click the sheet tab
of the first sheet that contains a value you want to include in your total
and click the cell that contains that value, type a plus sign and click the
sheet tab for the next value and click that cell, continue until you have
added all your values to the formula and then enter to complete.
Adding B25 from Jan, B22 from Feb, and B28 from Mar would look like this:
=Jan!B25+Feb!B22+Mar!B28
 
Thank you so much!

Gaurav said:
Assuming you have these values in cell A1 on all the three sheets and you
want the sum of these sheets in cell A1 of the sheet 4.

one way is to put this formula in Sheet4, Cell A1.

=SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1)

another way is to insert 2 new sheets. one before Sheet1 and one after
Sheet3. Name them as Start and End. then in Sheet4, enter this formula.

=SUM(Start:End!A1)

You can hide these 2 dummy sheets then.

if the cells are different on every sheet, you can just use the first
formula and change the cell references as needed.

Thanks
Click to expand...
 
Thank you...very helpful!

BoniM said:
If you're summing the same cell from multiple worksheets in the same workbook:
Select the cell where you want the total to appear, click AutoSum, click on
the first sheet in the range of sheets, hold down the shift key and click on
the last sheet in the range of sheets and then click the cell you want to
total from all sheets. Hit the enter key (or click the enter button on the
formla bar) to complete.
If you were totaling cell B25 from sheets named Jan thru Dec, it would look
like this:
=SUM(Jan:Dec!B25)
If the cells are not in the same location on each sheet, select the cell
where you want the total to appear, type an equal sign, click the sheet tab
of the first sheet that contains a value you want to include in your total
and click the cell that contains that value, type a plus sign and click the
sheet tab for the next value and click that cell, continue until you have
added all your values to the formula and then enter to complete.
Adding B25 from Jan, B22 from Feb, and B28 from Mar would look like this:
=Jan!B25+Feb!B22+Mar!B28
Click to expand...
 
Back
Top