Sum Across Multiple Tables

goohst,

Can you post a screenshot of the layout of the table(s). It's hard to help without some more information.

The arrangement of input data can greatly simplify (or, conversely, complicate) summarizing.

Wayne

G,

You'll want to use LOOKUP or MATCH to locate the row that Team 1 is in. I recommend that instead of summing across all tables in one cell that you create a summary table that has a row for each table and a column for each team, or vice versa.

Jerry

Hi g,

Here's one not particularly elegant way to accomplish this:

Week 1 through Week 4 are Data tables, with no formulas. The teams were reordered randomly, then the data added as shown.

The Summary tables sums the data in each column for the row in each tabe occupied by the team identified in coumn A.

There is one formula on Summary, entered in cell B2, then filled right and down to D10.

B2: =SUM(OFFSET(Week 1::$A$1,MATCH($A,Week 1 :: $A)-1,COLUMN()-1),OFFSET(Week 2::$A$1,MATCH($A,Week 2 :: $A)-1,COLUMN()-1),OFFSET(Week 3::$A$1,MATCH($A,Week 3 :: $A)-1,COLUMN()-1),OFFSET(Week 4::$A$1,MATCH($A,Week 4 :: $A)-1,COLUMN()-1))

The formula contains a block for each table. Each block is identical eacept for the table name in the first argument of OFFSET abd the second argument of MATCH. Here, several returns have been inserted into the formula to separate the blocks for easier comparison:

=SUM(

OFFSET(Week 1::$A$1,MATCH($A,Week 1 :: $A)-1,COLUMN()-1),

OFFSET(Week 2::$A$1,MATCH($A,Week 2 :: $A)-1,COLUMN()-1),

OFFSET(Week 3::$A$1,MATCH($A,Week 3 :: $A)-1,COLUMN()-1),

OFFSET(Week 4::$A$1,MATCH($A,Week 4 :: $A)-1,COLUMN()-1))

Provided each of the data tables is given a name unique within the document, the tables nd/or the summary table may be placed on separate sheets without requiring the addition of the sheet name to the addresses referencing each data sheet.

Regards,

Barry

INDIRECT will work well for this type of transfer/summation where the data on each table is being returned to a singe row on the summary table. That allows the position of that row to be used to determine which table the data for that row is to be fetched from. In this case, though, data for each cell is gathered from all of the tables in the data set (and not necessarily from the same cell on each of these tables).

That makes the assigning of Table names somewhat more difficult, even if the names differ only by the incremented number appended to their common names.

Regards,

Barry

Hi goohst,

You wrote:

I was really hoping for something a little more elegant, but appears I'm stuck with the manual lifting.

I was really hoping to be able to use indirect as a place holder for all of the weekly tables. Anyone had success with this?

Have a look back at Wayne's reply:

Can you post a screenshot of the layout of the table(s). It's hard to help without some more information.

The arrangement of input data can greatly simplify (or, conversely, complicate) summarizing.

1. I don't see a screenshot of your tables. That would certainly help other users to understand your aim.

2. Could you perhaps simplify your input data? For example, why are you using your 'presentation' tables as your lookup tables? (The problem position of Team 1 will vary from week to week.)

I suggest that you use a standard order of Team number and/or name in your weekly records. Use those tables to add values across all of the weekly sheets.

**Then** create a 'presentation' from your summary. Therein lies the power of Numbers: data tables, engine room, presentation - all can be on separate Sheets.

Regards,

Ian.