Help - Summing With a Many to Many Relationship

[steveschulte34]

Can someone kindly help me, I am so stuck! My boss made a workplan in Excel and we want to sum the number of hours each person has of work in a month across all tasks. The output table should show something like this
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]55
[/TD]
[/TR]
</tbody>[/TABLE]
The tricky part is, there are lots of tasks and each task may contain up to 5 different resources (not everyone is assigned to every task). So for each person, the summing, needs to look across all the tasks, for that month, and sum the hours for that month, only where there is a task with that person's name in it.
In psuedo code, for cell above ("Person 1" ; "Jan") it would be something like: (Look in the Workplan, and anywhere in the resource list section (resource 1 thru resource 5) you find "person 1" and they have hours assigned to "Jan", sum all of these across all tasks.


Please take a look at the linked workbook. In the “Resource Summary” tab, this is where I need the formula help (it seems to be giving me weird/incorrect results ->look at “person 4” or “person 10” vs. what they are actually assigned on the “Workplan” tab) https://drive.google.com/file/d/0B67R2lPaLfhiYzlGRUp5QW1XbVk/edit?usp=sharing
Thank you!

 

[shg]

I get a 404 error trying to download at the link.

 

[steveschulte34]

I get a 404 error trying to download at the link.

Let me try pasting it this way: https://drive.google.com/file/d/0B67R2lPaLfhiYzlGRUp5QW1XbVk/edit?usp=sharing
And this way [https://drive.google.com/file/d/0B67R2lPaLfhiYzlGRUp5QW1XbVk/edit?usp=sharing] in case it's getting truncated.

 

[shg]

The link works fine. When I click download I get the 404 error.

How about putting it on box.net?

 

[steveschulte34]

The link works fine. When I click download I get the 404 error.

How about putting it on box.net?

Got it! Ok let's try box.net, good suggestion - https://app.box.com/s/etv2nxn4qw4udjhw614r

 

[shg]

OK, steve, now I see it, but don't have a clue what it all means.

 

[steveschulte34]

Put simply, I have a project plan with a bunch of tasks. I need time from different people to complete different tasks. I need to figure out how much time each person is getting booked to tasks, on a per month basis.
That's essentially it. So that summary tab should look at each person, look at all of their tasks for a month and sum them up and return the results.

But it's giving screwy results (notably where I highlighted).

I think the problem has something to do with SumIf offsetting the month column that it's summing in. There is probably a better way to do this using MATCH where it matches the month at the top with the person name, but I have no clue how to do that here.