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

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…