Sumifs That Excludes Parents

Jarret.Birdwell

Hello,

I am working on an ever expanding project timeline that has 100's of parent tasks that flow into potentially up to 10+ levels of parent tasks, with children tasks at every level. Given the granularity of the task management and the timeline being outwardly facing, "=sum(children())" is used on every parent task to keep high level cost analysis for those viewing.

I am trying to show cost by task end date quarter projecting out the project's lifetime for the entered costs of the children tasks on a separate sheet.

We can refer to the sheets as 1 (bulk data) & 2 (summary of costs), and the IF criteria as Q (End Date Quarter) & C (Task Cost) (which names are shared between sheets for simplicity). In sheet 2 I use "=sumifs({1C},{1Q},=$[2Q]@row)" which gives me a sum of all the cost that match that end date quarter. I need to edit the formula to only sum children task costs.

Is there a way to exclude parent tasks? If not, is there a way to only sum parent tasks, that way I can subtract by the parents' sums?

juliesilverio

Hi Jarret,

I would add a helper column into your sheet and call it "Level" -- This is what we do at my organization to assign a number to each level in the hierarchy of a sheet and we can then use that number to include or exclude rows as needed. You can hide this helper column after adding it to keep your sheet streamlined.

The formula to assign a number to your rows is =COUNT(ANCESTORS())

You can then use the assigned numbers to update your formula to exclude your parent tasks. For example, if you only want to exclude parent tasks that are assigned a 0, your formula would be

=IF([Level]@row > 0, SUMIFS(({1C},{1Q},=$[2Q]@row)), "") -- the last part will leave the parent rows excluded blank but you can change that to say "Excluded" or anything else that makes sense with your data set.

Alternatively, you can skip the helper column but it usually helps me to visualize what I need to include and exclude. To skip the helper column, you could use

=IF(COUNT(ANCESTORS()) > 0, SUMIFS(({1C},{1Q},=$[2Q]@row)))

Hope that helps!

Jarret.Birdwell

https://community.smartsheet.com/discussion/comment/414084#Comment_414084

Hello Julie,

Thank you for the response, unfortunately excluding entire levels won't resolve the issue. The main sheet has over 2000 tasks in a hierarchy that tracks many long-term or complex tasks that may have several layers of long-term or complex tasks.

This is for a manufacturing facility build out and tracks everything from securing funding to finalizing purchasing property to finalizing engineering to purchasing equipment to equipment installation to creating documentation for operating the equipment. There are tasks at every level that need to be captured in my cost analysis, but having sums at every parent task to flow upward to the highest level highly inflates my costs.

John_Foster

Hi @Jarret.Birdwell,

I have the same requirement, what we did was have a helper column that counts whether a row has any children using the following formula

=COUNT(CHILDREN())

The total value for us is held on the Sheet Summry, where we do a SUMIF that only sums values where the helper column is equal to 0 as per the formula below.

=SUMIF([Child Count]:[Child Count], =0, [Budget Value]:[Budget Value])

Hope this helps!

John