Multi-level Sum Rollup

[Ash Kazor]

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

<tbody>
</tbody>

Level	Level Value	Parent SUMs
0	85	552
1	15	168
2	25	25
2	18	128
3	45	110
4	20	65
5	45	45
1	85	299
2	60	60
2	27	112
3	85	85
3	42	42
0	51	218
1	26	26
1	89	141
2	52	52

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[reberryjr]

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

, only the children directly nested underneath each "parent" should be summed together. Same-level children belonging to other "parents" would be excluded.

<tbody>
</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

	A​	B​	C​
1​	Level​	Level Value​	Parent SUMs​
2​	0​	85​	552​
3​	1​	15​	168​
4​	2​	25​	25​
5​	2​	18​	128​
6​	3​	45​	110​
7​	4​	20​	65​
8​	5​	45​	45​
9​	1​	85​	299​
10​	2​	60​	60​
11​	2​	27​	154​
12​	3​	85​	85​
13​	3​	42​	42​
14​	0​	51​	218​
15​	1​	26​	26​
16​	1​	89​	141​
17​	2​	52​	52​
18​

<tbody>
</tbody>

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...

	A​	B​	C​
1​	Level​	Level Value​	Parent SUMs​
2​	0​	85​	552​
3​	1​	15​	168​
4​	2​	25​	25​
5​	2​	18​	128​
6​	x​	70​	70​
7​	3​	45​	110​
8​	4​	20​	65​
9​	5​	45​	45​
10​	x​	60​	60​
11​	1​	85​	299​
12​	2​	60​	60​
13​	2​	27​	154​
14​	3​	85​	85​
15​	3​	42​	42​
16​	x​	25​	25​
17​	0​	51​	218​
18​	1​	26​	26​
19​	1​	89​	141​
20​	2​	52​	52​
21​

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