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


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.
    Genevieve P. Employee Admin

    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.


    =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!



  • Kim Michael
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    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!