6 Replies Latest reply: Sep 16, 2013 9:19 AM by Yellowbox
goohst8 Level 1 Level 1 (0 points)

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?

  • Wayne Contello Level 6 Level 6 (15,520 points)

    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

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    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

  • Barry Level 7 Level 7 (29,215 points)

    Hi g,

     

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

    Screen Shot 2013-09-15 at 6.41.51 PM.png

    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

  • goohst8 Level 1 Level 1 (0 points)

    Thanks for taking the time to read and respond.

     

    Barry, you have the basic idea of what I'm asking and the problem I'm running into, as well. 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?

  • Barry Level 7 Level 7 (29,215 points)

    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

  • Yellowbox Level 5 Level 5 (7,075 points)

    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.