Multi-level Sum Rollup

[Ash Kazor]

<tbody>
[TD="class: votecell"]
[/TD]
[TD="class: postcell"] Hello. I'm trying to write a formula that would achieve the values in the "Parent SUMs" column, but it's proving a bit of a challenge. Essentially, my goal is to sum all "children" or sub-level values under each "parent" (including the value that resides with that "parent"). To clarify, only the children directly nested underneath each "parent" should be summed together. Same-level children belonging to other "parents" would be excluded.

Thank you for the help!

-AK

[/TD]

</tbody>

[TABLE="class: grid, width: 227"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Level
[/TD]
[TD]Level Value
[/TD]
[TD]Parent SUMs[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]85[/TD]
[TD]552[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15[/TD]
[TD]168[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]18[/TD]
[TD]128
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]45[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]45[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]85[/TD]
[TD]299[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]60[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]27[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]85[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]42[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]51[/TD]
[TD]218[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]26[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]89[/TD]
[TD]141
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]52[/TD]
[TD]52
[/TD]
[/TR]
</tbody>[/TABLE]

 

[reberryjr]

With the table you provided, how can we determine "children" or "sub-level", or the piece below?

<tbody>
[TD="class: votecell"][/TD]
[TD="class: postcell"], only the children directly nested underneath each "parent" should be summed together. Same-level children belonging to other "parents" would be excluded.[/TD]

</tbody>

 

[Ash Kazor]

Hi reberryjr. Thanks for your response. For example, level "0" is a parent and it has 11 different children (1,2,2,3,4,5,1,2,2,3,3). Some of those children are parents with children of their own. This example actually stems from a Bill of Material, where there are top-lvl assemblies, and lower-lvl assemblies underneath. So lvl-0 is a top-lvl assembly. Anything underneath the 0 is a sub-assembly. Let's say the 2nd column is the cost of each individual level. I'm trying to come up with a way to easily sum all costs pertaining to all of the children underneath a parent (end result in column 3).

So for example, if we're looking at the first lvl-3, it has children "4" and "5" underneath. So the cost of the lvl-3 parent would be 45+20+45, hence the 110 in the 3rd column.

Please let me know if this helps to clarify.

Thanks again!
-AK

 

[Marcelo Branco]

Maybe..
Remark: i found a different result in row 11: 154 (27+85+42) instead of 112


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Level​

[/TD]
[TD]

Level Value​

[/TD]
[TD]

Parent SUMs​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

85​

[/TD]
[TD]

552​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

15​

[/TD]
[TD]

168​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

25​

[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

18​

[/TD]
[TD]

128​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

3​

[/TD]
[TD]

45​

[/TD]
[TD]

110​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

4​

[/TD]
[TD]

20​

[/TD]
[TD]

65​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

5​

[/TD]
[TD]

45​

[/TD]
[TD]

45​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

1​

[/TD]
[TD]

85​

[/TD]
[TD]

299​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

2​

[/TD]
[TD]

60​

[/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

2​

[/TD]
[TD]

27​

[/TD]
[TD]

154​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

3​

[/TD]
[TD]

85​

[/TD]
[TD]

85​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

3​

[/TD]
[TD]

42​

[/TD]
[TD]

42​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

0​

[/TD]
[TD]

51​

[/TD]
[TD]

218​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

1​

[/TD]
[TD]

26​

[/TD]
[TD]

26​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

1​

[/TD]
[TD]

89​

[/TD]
[TD]

141​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

2​

[/TD]
[TD]

52​

[/TD]
[TD]

52​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=SUM(B2:IF(A2>=A3,B2,INDEX(B3:B$1000,MATCH(1,INDEX(--((A3:A$1000<=A2)+(A3:A$1000=0)+(A3:A$1000="")>0),),0)-1)))

Hope this helps

M.

 

[reberryjr]

Ash, perhaps I'm still not understanding your goal, or the structure of the data is throwing me off. Levels don't seem to have standard values assigned to them in column B. A formula could be developed to sum 3 rows together, but I'm thinking it's not consistent all the way through. In your example, are you saying you need to add row 6 + row 7 + row 8?

 

[Ash Kazor]

Marcelo - this worked perfectly. Thank you so much for the help, I really appreciate it!!

Ash

 

[Ash Kazor]

Hi Marcelo. Thank you again for your help with the below solution. I do have one follow-up question. Below is the output from the table you provided with new orange lines added. If I have non-numeric "levels" within my list (anything with an "X" value) that I would like excluded/ignored from the Parent SUMs calculation in column C, would that be an easy modification to your formula? Any suggestions would be greatly appreciated.

Thank you,
Ash

 

[Marcelo Branco]

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Level​

[/td][td]

Level Value​

[/td][td]

Parent SUMs​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0​

[/td][td]

85​

[/td][td]

552​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

15​

[/td][td]

168​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2​

[/td][td]

25​

[/td][td]

25​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2​

[/td][td]

18​

[/td][td]

128​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

x​

[/td][td]

70​

[/td][td]

70​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

3​

[/td][td]

45​

[/td][td]

110​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

4​

[/td][td]

20​

[/td][td]

65​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

5​

[/td][td]

45​

[/td][td]

45​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

x​

[/td][td]

60​

[/td][td]

60​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

1​

[/td][td]

85​

[/td][td]

299​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

2​

[/td][td]

60​

[/td][td]

60​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

2​

[/td][td]

27​

[/td][td]

154​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

3​

[/td][td]

85​

[/td][td]

85​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

3​

[/td][td]

42​

[/td][td]

42​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

x​

[/td][td]

25​

[/td][td]

25​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

0​

[/td][td]

51​

[/td][td]

218​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

1​

[/td][td]

26​

[/td][td]

26​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td]

1​

[/td][td]

89​

[/td][td]

141​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td]

2​

[/td][td]

52​

[/td][td]

52​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in C2 copied down
=IF(A2="x",B2,SUMIF(A2:IF(A2>=A3,A2,INDEX(A3:A$1000,MATCH(1,INDEX(--((A3:A$1000<=A2)+(A3:A$1000=0)+(A3:A$1000="")>0),),0)-1)),"<>x",B2:IF(A2>=A3,B2,INDEX(B3:B$1000,MATCH(1,INDEX(--((A3:A$1000<=A2)+(A3:A$1000=0)+(A3:A$1000="")>0),),0)-1))))

M.

 

[Ash Kazor]

This works perfectly!! Thank you for all your help Marcelo! - Ash