# Cross cell formula

Options
✭✭✭✭

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?

• Employee
Options

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)

=((Duration@row * 8) * [Allocation %]@row) * COUNTM([Assigned To]@row)

Let me know if this works for you!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

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

• Employee
Options

No problem at all! Happy I could help 😊