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.

Martin Hammond

If it is of help to anyone I worked out how it correctly displays for me:

=SUMIFS([Allocation %]:[Allocation %], [Start Date]:[Start Date], >=[Start Date]@row, [End Date]:[End Date], <=[End Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)

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.

Martin Hammond

Thanks Basam that has helped a lot and given me food for thought on how to display capacity

Bassam Khalil

@Martin Hammond

You are welcome

Martin Hammond

Actually taking a look it the formula doesn't contain the date ranges so I do not think it would be correct, it would repeat the same sum regardless as it would not know what date range to search in

Martin Hammond

If it is of help to anyone I worked out how it correctly displays for me:

=SUMIFS([Allocation %]:[Allocation %], [Start Date]:[Start Date], >=[Start Date]@row, [End Date]:[End Date], <=[End Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)