Cross cell formula
I am trying to create a formula that will represent the estimated hours per task for all resources allocated for that task
Currently:
Duration is in days
"Allocation % is 5% for the duration of the task
"Estimated Work Hrs": =((Duration11 * 8) * [Allocation %]11) 8 is reflective of hrs in a day
Since I have more than one resource I want my "Estimated Work Hrs" to reflect total for all resources working on the task
In simple math it would be
line 1: [ (8 x 8) x5% ] x 1 = estimated hours total for task
line 2: [ (1 x 8) x5% ] x 2 = estimated hours total for task
Any suggestions?
Answers

Hi @Kim Michael
You can use the COUNTM function to count how many resources are listed in that Assigned To column, and use that number as your multiplication at the end.
Ex:
=COUNTM([Assigned To]@row)
So, in your total formula:
=((Duration@row * 8) * [Allocation %]@row) * COUNTM([Assigned To]@row)
Let me know if this works for you!
Cheers,
Genevieve

This is exactly what I am a looking for but now I have run into another issue. The formula works exactly the way I need it to but if I have a PM that does not have any resource in the Assigned To column the total for the formula will be 0. What additional part can I add to the formula that says
=((Duration@row * 8) * [Allocation %]@row) * COUNTM([Assigned To]@row) and if no resources count as if it were 1 resource

Hi @Kim Michael,
No problem! We can use an IF Statement to first check if there's a resource. If there is no one in the Assigned To column, it will say 1, otherwise it will use the COUNTM formula.
Try this:
=((Duration@row * 8) * [Allocation %]@row) * (IF([Assigned To]@row = "", 1, COUNTM([Assigned To]@row)))

You are awesome. Thank you so much. Now I can give accurate estimated hours. Far better than the inaccuracy we had prior.

No problem at all! Happy I could help 😊
Help Article Resources
Categories
Check out the Formula Handbook template!