Welcome!

Is there a formula that will work for this?

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.

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Which formula can be used to extract the values with in bracket in a multi value cell.
I have a dropdown containing multiple values and I need to extract only the values like [CDB0040] [CDB0038]
How to find hours in any period
Hi All, I need to calculate the MAX of the blank entries in a timecard for any 24 hour period. For example: Start counting the first row from 00, then it has 12 empty fields under each hour column, and results with 12 hours of non-work.. Next step is to check how many hours of non-work is present starting from first row's…
Unable to Manage Scheduled Report Recurrence (Options Disabled)
This discussion was created from comments split from: Can't Edit or Delete "Send as Attachment" Recurrence.