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

Find more posts tagged with

Accepted answers

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

All comments

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.

Thanks again

Nick

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.