How can I calculate the percentage a resource is allocated to each project?

Mike Hadddock

How can I calculate the number of days or allocation percentage a resource is assigned to a project? What I'm trying to understand is the percentage a resource is allocated to a project so I can track the total in a master project list. For example, if a resource is assigned to 3 projects and they are aloocated 25% per project - how can I calculate that per project so I can compile into a master project project or dashboard? 

Matt Hines

Do you have a single sheet for each project (I assume this) or one sheet with multiple projects. If one sheet per project, you could have a "By Resource" Summary at the bottom where you list all resources and a count of how many times each resource is listed within the details of the project in the above section. You could figure out percentage of resource / total needed per project and then link this summary section out to another sheet. You could even add number of days along with this summary and you would be able to figure out both by resource allocation and how much time is used by each resource.

 

I do something similar with our financial month close checklist and the summary section I have integrated with Zapier and Google Sheets for a very simple Dashboard with graphs of the over all status of the close.

Mike Hadddock

Thanks Matt. I'm trying to figure out the formula's required for your suggestion as it sounds like a great way to get wat I'm after.

 

 

Matt Hines

To explain further, I have two sections. The meat which is the detailed checklist. and then the summary section that is just the resource list. For the tasks completed I do a COUNTIFS the name matches the list AND the Checkbox is 1 (checked). For the total I just do COUNTIFS name matches the list. Then just division to get percent complete.

 

Hope this offers a little more insight.