Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Mike Hadddock
edited 12/09/19 in Archived 2016 Posts

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.

This discussion has been closed.