Trying to calculate an "on this day" capacity

Martin Hammond

Hi There,

I have a master sheet of multiple projects. I am trying to work out how an allocated resource can show if it is over capacity in a column - similar to the resource view but so it shows in the sheet itself.

So If I have

Task

Start Date

End Date

Duration

Assigned to

Allocation %

Hours per day

I want to look up the allocation % between the start date and end date for [assigned to] and then do a lookup of all Rows where assigned to resource is mentioned and calculate the allocation %

1 Jan - 5 Jan, Task 1, Resource 1, 50% allocation - Column that shows Total allocation 150%

1 Jan - 3 Jan, Task 2, Resource 1, 100% allocation - Column that shows Total allocation 150%

I just cannot work out the formula to do this

Thanks,

Martin

Bassam Khalil

Hi @Martin Hammond

Hope you are fine, as per your question the following formula will do what you need

=SUMIFS([Allocation %]:[Allocation %], [Assigned to]:[Assigned to], [Assigned to]@row)

but as a planning Engineer, i can say to you this is not correct because in the above example you can see that the Total allocation for Resource 1 is 150% only from ( 1-Jan To 3-Jan ) but from ( 4 To 5 Jan ) his allocation is 50% only. so you need to think in a different approach.

maybe you need to do a summary table in a helper sheet contain all Resource in your project in a rows and the day of the month in the columns and calculate for each day if the Resource is overallocated or not.