Help - Summing With a Many to Many Relationship

steveschulte34

New Member
Joined
Jan 14, 2014
Messages
4
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
NameJanFebMar
Person 142
Person 21755

<tbody>
</tbody>
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
I get a 404 error trying to download at the link.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
The link works fine. When I click download I get the 404 error.

How about putting it on box.net?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
OK, steve, now I see it, but don't have a clue what it all means.
 

steveschulte34

New Member
Joined
Jan 14, 2014
Messages
4
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top