Cross cell formula

Kim Michael

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?

Genevieve P.

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

Kim Michael

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

Genevieve P.

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)))

Kim Michael

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

Genevieve P.

No problem at all! Happy I could help 😊