Calculating Monthly Labor Hours From Project Sheet

Greetings,

I am trying to pull labor hours by month from my project sheet. For some reason the below formula seems to work but it also attempts to calculate the values in the parent task which is =sum(children()). I know something is wrong with my formula below. I need to pull labor for each month and I am running into a roadblock. Any help would be much appreciated.

=SUMIFS({PV Dry Park Actual Labor Hours - Monthly}, {PV Dry Park Start Date}, >=(DATE(2024, 1, 1)), {Photo Validation Dry park Range 1}, <=(DATE(2024, 1, 31)))

Find more posts tagged with

Accepted answers

All comments

JamesB

@RJT This is happening because your parent rows have the dates also in the columns. IF you had a helper column and use the formula =Count(Children()) as a column formula, then you can add this to your SUMIFS to only pull where the count is 0. This will make sure that you are pulling the lowest level indent data and skipping any row that is a parent to other rows.

RJT

Ok. When you say helper column what is this. I am new to smartsheet. I have added another column and applied the =COUNT(CHILDREN()). I am using the below formula to try and calculate the labor hours for each month. It only works when I remove the end dat within the range. If i have a task that started on 1/1/24 but ended on 2/15/24 it will not sum the value properly. Is there a better way to pull the sum of labor hours for the past 30 days only regardless of what the end date is? I truly appreciate your help

SUMIFS({P903 - Walkthrough Actual labor Hours}, {P903 - Walkthrough Start Date}, >=(DATE(2024, 4, 1)), {P903 - Walkthrough End Date}, <=(DATE(2024, 4, 30)))

JamesB

@RJT

In Smartsheets, a helper column as Smartsheet users colloquially call it, is a column that is created to give you a metric or qualifier to formulate against. In this instance, using the count Children() formula you can identify those rows that have no child tasks. If I am understanding your ask, I think this would be the formula you would apply.

=SUMIFS({P903 - Walkthrough Actual labor Hours}, {Count Children Reference Column}, 0, {P903 - Walkthrough Start Date},>=TODAY(-30))

The above formula will look for any row where the children count column has a 0, then any cell where the start date is greater than or equal to Todays date -30 days. Because it looks at the current date on your computer, everyday it will move the needle to only look at the past 30 days for the start date.

Now if you want it by month base on Start Date, then you would use the following formula. This will give you hours where the start date is equal to or between 1/1 and 1/31 of 2024.

=SUMIFS({P903 - Walkthrough Actual labor Hours}, {Count Children Reference Column}, 0, {P903 - Walkthrough Start Date},AND(@cell <= DATE(2024,1,31), @cell >= DATE(2024,1,1)))

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.