# 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?

• 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

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!