# Trying to calculate an "on this day" capacity

Options

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

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

Tags:

• ✭✭✭✭✭✭
Options

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.

bassam.khalil2009@gmail.com

Options

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)

• ✭✭✭✭✭✭
Options

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.

bassam.khalil2009@gmail.com

• Options

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

• ✭✭✭✭✭✭
Options

You are welcome

bassam.khalil2009@gmail.com