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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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)))
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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 😊
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!