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,


Best Answer

  • Nick.C
    Nick.C ✭✭✭
    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



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!