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

Combining a few columns into one --can I nest IF statements?
My current formula is =IF([To whom will the sample go?]@row = "Customer/Shipped out", "Shipping Date: " + [Date to be Shipped]@row + CHAR(10) + [Ship Method]@row + CHAR(10) + CHAR(10) + [TR Full Contact Info]@row , "") I need this to be able to look at [TR Full Contact Info]@row and if blank, pull the information from [IN…
Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)