Resource Count & Effort Calculation
Hi there, I am struggling with two calculations, namely resource count and effort. Any assistance would be really appreciated.
For resources, I am using =COUNTM([Assigned To]@row), as there can be more than one resource per task. The problem is, this formula works at the lowest level of the task hierarchy, but it just shows 0 at the highest level. I need this formula to work in order for the effort formula to work at the highest level.
The formula I am using for effort is:
=IFERROR((Duration@row * [Resources]@row * [Resource Allocation %]@row), " "). This also works at the lowest level, but not at the highest level. It shows 0 at the highet level.
Many thanks,
Nick
Best Answer

Thanks so much Jmyzk! Much appreciated. I would never have thought of that.
It worked with one exception though. Since, my hierarchy has milestones in it (i.e. where there is a 0 in the duration column) the count descendants formula unfortunately counts milestones as a step when it should ignore it. Don't suppose you know a way around this?
Thanks again
Nick
Answers

Hi @Nick.C
If I need to change the formula between a parent row and a child row, I will use a helper column to determine if a row is a parent or child row.😀
Example helper column
[D] : =COUNT(DESCENDANTS())

Thanks so much Jmyzk! Much appreciated. I would never have thought of that.
It worked with one exception though. Since, my hierarchy has milestones in it (i.e. where there is a 0 in the duration column) the count descendants formula unfortunately counts milestones as a step when it should ignore it. Don't suppose you know a way around this?
Thanks again
Nick
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!