Is there a formula that will work for this?

Darcie Wilson

Please see attached screenshot

In a separate field (that I haven’t yet created), I would want to know the % availability (based on 40hr work week) of each assigned resource based on each active request status.

 

Shaine Greenwood

Hi Darcie,

You might need to test this out a bit more extensively, but I was able to come up with a formula for resource allocation based on your sheet: 

=SUMIFS([Est Hours]:[Est Hours], [Assigned To]:[Assigned To], "Person A", [Request Status]:[Request Status], OR(@cell = "In Process", @cell = "Approved By Mgr")) / 40

This summarizes all of the estimated hours of projects that have been approved and are assigned to "Person A." It then divides them by 40 to give you their percent allocation. 

A few notes on this:

• You'll need to modify the formula for each of your resources.
• Change the column names in my example formula to the column names in your sheet.
• You'll want to put this in a column that is formatted for percentages.

Let me know if you have any questions.