adding subtotals from several worksheets

  • Thread starter Thread starter bernieb
  • Start date Start date
B

bernieb

I am trying to figure out how to create a "Grand Total" on my final worksheet
containing the sum of subtotals from several previous worksheets. Such as,
cell O33 from worksheet1, cell O44 from worksheet2, cell O58 from
worksheet5....etc.... not sure where to even find a HELP item on this.
 
Here's a *REMOTE* possibility making *a lot* of assumptions**:

If these cells are SUMS then there's a good chance** that these values are
the max values in those columns.

Your sheet names follow some kind of sequential pattern** like Sheet1,
Sheet2, Sheet3, etc.

The sheets you want totaled are a contiguous block** like
SUM(Sheet1,Sheet2,Sheet3), not like SUM(Sheet1,Sheet5,Sheet22)

Based on all of those improbable assumptions, this formula will sum the max
values from sheet's 1:3 column O:

=SUMPRODUCT(SUBTOTAL(4,INDIRECT("sheet"&ROW(INDIRECT("1:3"))&"!O:O")))
 
Type an = sign in the cell where you want your Grand Total, then go to the
first sheet and click in the relevant cell to add, then type + sign, then
click in the relevant cell on the next sheet, then another plus sign, on
until you get to the cell on the last sheet, and enter.

You should end up with a formula like =Sheet1!O33+Sheet2!O44+Sheet5!O55
 
Back
Top