Calculate Project Time TOTAL for PM across aligned projects

Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example:

<3 months = 25 hours

3-6 months = 75 hours

6+ months = 125 hours

As an example I have about 20 projects aligned to me between all of these ranges and I would like to get a Total Project Time. I am not sure how I achieve the SUM of the Total Project Time. Thank you for any help!

Find more posts tagged with

Formulas

Accepted answers

All comments

jjg279

Hey @JNash, without knowing all your column names and ranges would something like this work:

=SUMIFS({07. Operations Project List Help Time}, {07. Operations Project List Resource Type}, "Project Manager", {07. Operations Project List Resource Name}, [Column2]@row)

SUMIFS are formatted a little differently from some of the other tools ([range of summing], criteria range, criteria, criteria range2, criteria2, etc…)

JNash

Tried that (I think) and it came back the same but I understand need to range first vs putting it last like I was doing.

Any other thoughts?

jjg279

@JNash, in that formula above you have an extra parenthesis at the end, if you get rid of that does it work?

JNash

Removing 1 paratheses at the end didnt do anything :(

JNash

I changed my rows and columns, I did think formula and it came back "0" so I feel we're getting a little closer:

=SUMIFS({07. Operations Project List Help Time}, {07. Operations Project List Resource Type}, "Project Manager", {07. Operations Project List Resource Name}, [Column 3]$2)

I also wanted to share the Range Help Column… I only have hours within the parent row so align with the project data. Child rows contain other project participant information. Not sure if that is impacted the hours calculation.

jjg279

Hmmm… Are any of your columns formatted as a contacts column? And is your {07. Operations Project List Resource Type} a dropdown (or multidropdown?)

JNash

Resource Type and Resource Name columns are both dropdowns (select only 1 option for each) to ensure spelling/options are selected/no accidental spelling variations.

jjg279

Oh phew. Good practice that.

I am wondering if it is returning zero because the helper column doesn't have numbers in the individual rows. I haven't worked a lot with Parent/children formulas, but what if you added another helper column "Child-Time" and filled it with

=PARENT([Helper (Time)]@row )

then changed your "sumif" to the following where Child Help time is the column created above:

=SUMIFS({CHILD HELP TIME}, {07. Operations Project List Resource Type}, "Project Manager", {07. Operations Project List Resource Name}, [Column 3]$2)

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.