## Sum Across Multiple Tables

385 Views 6 Replies Latest reply: Sep 16, 2013 9:19 AM by Yellowbox
Calculating status...
Currently Being Moderated
Sep 15, 2013 2:38 PM

Hello All, I'm hoping you can help me with this problem. I've spent quite a bit of time searching the archives and trying different formulas with no success.

I have a file with multiple tables that each represent weekly results for a league that I run. I would like to create a summary table that would add all values across all of the weekly sheets based on a criteria. So basically, I want to have a table that sums all the results for each individual team. For example, add all of the points for Team 1 across all weeks. The problem position of Team 1 will vary from week to week.

I've tried using Indirect, sumif, etc., but can't seem to get it to work. Is there a way to do this?

• Level 6 (11,965 points)
Currently Being Moderated
Sep 15, 2013 6:18 PM (in response to goohst8)

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

• Level 7 (27,430 points)
Currently Being Moderated
Sep 15, 2013 6:23 PM (in response to goohst8)

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

• Level 7 (28,825 points)
Currently Being Moderated
Sep 15, 2013 6:59 PM (in response to goohst8)

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

• Level 7 (28,825 points)
Currently Being Moderated
Sep 15, 2013 10:27 PM (in response to goohst8)

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

• Level 4 (3,640 points)
Currently Being Moderated
Sep 16, 2013 9:19 AM (in response to goohst8)

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.

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.