Resource Count & Effort Calculation

Nick.C

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

Nick.C

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

jmyzk_cloudsmart_jp

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

https://app.smartsheet.com/b/publish?EQBCT=c11796531b0b4654bd310786003572db

Nick.C

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