Roll Up Calculation using separate sheet

LDLValentine

I started with a template, but our resource management is a bit more complex than the template, so I am making adjustments to accommodate for our roadmap requirements. Of course this meant digging into the calculations. The template uses two sheets, PTO & Project Staffing + Staff Resource Rollup. The Staff Resource Rollup is where I should be able to see where I have constraints, shortfalls, and availability (yes, I know I could use RM and would love to but so far we haven't invested so I have to make do).

The current calculation using in Staff Resource Rollup:

=SUMIF({Project Staffing Range 1}, Person4, {Project Staffing Range 2})

Assume: Person 4 is on row 4 and Project Staffing Range 2 is Week 1 of a 52 week year

I have 37 resources in house + Consultants, though I don't plan on tracking them here I do have to account for them for budget purposes.

There has to be an easier way to write this! It will not copy (drag) down or over. Thoughts and Ideas would be most welcome.

Paul Newcome

Are you able to provide some screenshots for context?

LDLValentine

Sure:

Here is where we assign both PTO and resources to projects. We could have up to 5 projects running and some of our resources are assigned across multiples. Of course, this is where the issue arises from.

The team is organized by type, a project manager could put a request in for a BA or a Workstream Lead and a coordinator could potentially assign them for a period without knowing they are already at or over capacity.

This is where I am running into the issue. Where the calculation is painful. Right now, it is column by column as stated above. There has to be a better way to do this! I have tried an Index/Match, but must have done something wrong.

Hope this helps for context. With 52 weeks in the year, 38 resources to juggle across 5+ projects I am at my wits end.

Genevieve P.

Hi @LDLValentine

You are correct that with your current set-up having 52 weeks as 52 columns that you'll need 52 individual {references} in that formula.

You can simplify it by using @row (see here) instead of "row 4" as 4, so that it will be flexible looking at the user:

=SUMIF({Project Staffing Range 1}, Person@row, {Project Staffing Range 2})

This will allow you to drag-down so for this Week 1 it will drag-fill for all your users (or you could use a Column Formula). However for each of your weeks you will need to delete/re-add the {range 2}.

I would recommend renaming the ranges so they're easier to identify:

=SUMIF({Person}, Person@row, {Week 1})

Then in the next column, delete out {Week 1} fully and add in a new reference to week 2:

=SUMIF({Person}, Person@row, {Week 2})

As a column formula, and then the next column:

=SUMIF({Person}, Person@row, {Week 3})

And so on. Does that make sense?

Cheers,
Genevieve

LDLValentine

It does, thank you. As I am still refining the entire structure, I think this will help.