Please Help SUMIF Question with Parents and Children

Tamara

I'm working on a project sheet with dozens of Parent rows, each with a various amount of Children, ranging from one to over twenty. I have a user inputted column to assign known work hours needed for each Child's job, and am trying to create an independent running total for each Parent/Child group. This will allow me to more accurately stagger job start schedules.

The SUMIF function tallies a running total, but for the entire column. Does anyone know, is it possible to apply the SUMIF function independently to Parents and Children?

For example, if Parent #1 has Children with the following work hours, 5, 8, 4, 2 --> I'm looking for this to be tallied as a running total, within that group, as, 5, 13,17, 19.

Then if Parent #2 has Children with the following works hours, 1, 2, 3, 4, 5, 6 ---> I'm looking for this to be tallied as a separate running total, within that group as, 1, 3, 6, 10, 15, 21.

This would then need to continue, restarting for each Parent/Child group.

Does anyone know if this is possible and if so, how I can do this? I'm very perplexed on this one. Thoughts?

Thank you in advance for any help. Stay well, Tamara

Paul Newcome

If I am understanding correctly what you are trying to do, then you should be able to include the date criteria in the SUMIFS.

=IF(COUNT(ANCESTORS(Base@row)) = 0, MAX(CHILDREN()), SUMIFS(Numbers:Numbers, Parent:Parent, Parent@row, Row:Row, @cell >= Row@row, [Ship Date]:[Ship Date], @cell = [Ship Date]@row))

Paul Newcome

Are you working from the top down or the bottom up?

Tamara

Hi. The answer in the other reply provides more detail on the logic. It works best from the bottom up, for accuracy. Right now, all I’ve been able to pull off is from the top down. I’m not using SUMIF, but found another way. However, it’s flawed because if changes are made, added/deleted rows all the linked cells show “blocked”, until the running total calculation column is re pulled. If a column is sorted, it doesn’t reapply the formula correctly post sort, but instead leaves the previous calculated totals at row, until the column is re pulled.

I think SUMIF is the solution but don’t know how.  Thank you so much for thinking about this. 

Paul Newcome

Ok. I may have a few ideas. Am I working with you on another post? This seems familiar to another thread. But so I have this clear... You want to automate:

Parent..........33

5...................33

10.................28

6...................18

8...................12

4...................4

The column on the left is the hours and the column on the right is summing from the bottom up?

Tamara

Yes, this is exactly right. Thanks!

Paul Newcome

Go it. I will do some testing and get back to you next week.

Paul Newcome

I was actually able to get my first idea to work, so lets give this a whirl...

Add 2 columns. I used Row and Parent as my column names.

Row:

=COUNTIFS(Base$1:Base@row, OR(@cell = "", @cell <> ""))

Put that in row 1 and dragfill down. This will essentially replicate the row number in a way that we can use it in formulas.

Parent:

=IF(COUNT(ANCESTORS(Base@row)) <> 0, PARENT(Base@row))

Put that in every row.

Calculation Column:

=IF(COUNT(ANCESTORS(Base@row)) = 0, MAX(CHILDREN()), SUMIFS(Numbers:Numbers, Parent:Parent, Parent@row, Row:Row, @cell >= Row@row))

Put that in every row and you will have your "summing from the bottom up" in the child rows and the total will be duplicated in the parent row.

HERE is my sheet for reference...

Tamara

Hi Paul,

Thank you so much for your brilliant solution.  It works perfectly to sum the running total for all of a parent’s children (ancestors), and then start again. It handles jobs with the same Ship Date wonderfully. I’m still trying to figure out how to best handle Ship Date variations when part of the same job, Parent/Children. So far, using your SUMIF/@Cell solution, I’m creating a new Parent for each Ship Date group, even when in the same job, (e.g. J #1 Part A, J#1, Part B…..). This works, pulling an accurate running total for each Ship Date "sub-group", treating each Ship Date grouping almost as a separate job.

Looking forward to your thoughts on the other question and another (better) possibility to handle the date differences within the same job. The depth of your understanding amazes me. I’m learning more and more from reading your contributions to other posts.  MANY THANKS!!!!

Paul Newcome

I'm not sure I follow. Do you mean something like the below where "child" rows roll up to the Part rows which in turn roll up to the "Job" rows?

Job 1

-----Part A

----------child a 1

----------child a 2

-----Part B

----------child b 1

----------child b 2

Job 2

-----Part A

----------child a 1

----------child a 2

-----Part B

----------child b 1

----------child b 2

Tamara

Paul,

Yes, currently the way I’ve been able to get all of the dates to calculate properly is by splitting them up by Ship Date. Since the Running Total calculations are running between Parent Lines the different Ship Dates are almost being treated as new jobs. As shown in the first example. Originally, I thought this would work but learned there are several times dates change throughout the production process. This creates a lot of work to maintain.

Is there any way to get the dates to calculate correctly, under one Parent Row without splitting them into different sub groups? Like shown in the second example.

It's not my intention to make this more confusing but hope my explanation makes sense to you.

Thanks again, Tamara

Example 1

Example 2

Paul Newcome

Are you trying to essentially merge 8A and 8B with 8C? Have you tried removing those two parent rows and just making them all children of 8C?

Tamara

Hi Paul,

The summing formula you provided works perfectly to total the Cumulative QC and Assembly/Wiring Hours. However, my Max date formula doesn't recognize differences in ship dates within a job. The only way I've been able to accurately calculate all 3 sets of dates is to separate them with the parent lines, (the Ship Date is user entered). Is there a way to differentiate ship dates, accounting for the jobs cumulative hours from the bottom up. I'm guessing possibly an Index formula, but I am not sure.

I've included two examples below. The ideal format would be to have one parent per job and determine the max Ship date and max QC date off of each base/max date. I pasted the calculations at the very end.

Example 1

All of the dates Parts Due, Start Date, and To QC are correct in this example. To achieve this, I had to incorporate the parent row for the sheet to recognize both the date change and restart the associated cumulative totals by each date range.

Example 2

The dates are inaccurate in this example. This is my team's requested sheet format because of the high number of jobs and date changes. However, when the parent row is removed all of the cumulative totals meld together and the max date range automatically goes to the last shipping item, throwing anything with an earlier ship date behind.

Here are the calculation used to determine the dates,

Any help with this is so greatly appreciated. I've had such a headache with this and feel lost now beyond words.

Thanks again and again, Tamara

Paul Newcome

I don't understand what you are asking for. Are you trying to replicate the function of the parent rows without the actual parent rows?

Tamara

Good morning Paul,

Is the logic outlined possible with smartsheet, under one Parent Row? 

1.      Parent Row with "x amount' of Children, (1 to 50).

If any Ship Dates are different, then treat each matching range of ship dates independently, (almost like a separate job), summing the cumulative build-hours associated with that range, then applying the date formulas to that range.

Example

(PARENT ROW), with (11) Children

Ship Dates

(4) Children with 7/06/20 Ship Date, (Sum Cumulative Build-Hours assigned to these four), then apply Parts Due, Start, and To QC date formulas to this date range group, using the "max ship date" of this range.

(7) Children with 8/27/20 Ship Date, (Sum Cumulative Build-Hours assigned to these seven), then apply Parts Due, Start, and To QC date formulas to this date range group, using the "max ship date" of this range.

Is this possible with Smartsheet? 

Thanks, Tamara

Paul Newcome

If I am understanding correctly what you are trying to do, then you should be able to include the date criteria in the SUMIFS.

=IF(COUNT(ANCESTORS(Base@row)) = 0, MAX(CHILDREN()), SUMIFS(Numbers:Numbers, Parent:Parent, Parent@row, Row:Row, @cell >= Row@row, [Ship Date]:[Ship Date], @cell = [Ship Date]@row))

Tamara

Paul,

My apologies it took so long to get back to you. I have put the finishing touches on this project. It's working for my team. Couldn't have pulled this off without your help. Thank you so very much. I truly appreciate it!