Multi-Level Parent Summation (BOM type)

Hi, I'm looking for a formula that can sum BOM-style levels, where a higher number (e.g 5) is always a child of a lower number (e.g. 4). I've seen some formulas that are close, but I can't tweak them to get exactly what I want. Hope someone can help!
In the example below, the first column is the level number, the second is the values that will have been entered. The Third column is what i want the formula to calculate.

Level	Child Values	Parent / Total SUMS
0		130
1		43
2	25	25
2		18
3		18
4		18
5	18
1		87
2	27	27
2		60
3	30
3	30
0		115
1	26	26
1		89
2		89

Hello LucyL and Welcome to Excel Forum.
This provides the numbers down to the second zero which I assume starts a new product:

Formula:

Please Login or Register  to view this content.

In the future, please utilize the information in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page to attach a file to your posts.
Let us know if you have any questions.

Originally Posted by nick.williams

Does this do the trick?

=SUM(IF(ROW(A2:$A$20)-ROW(A2)<IFERROR(MATCH(TRUE,A3:$A$20<=A2,0),ROWS(A3:$A$20)),B2:$B$20,0))

I'm a bit confused why the parent/total SUMS columns seems to sometimes include the child values and other times not. e.g. why is it 25 in row 4 (the first row of level 2) but blank in rows 12 and 13? Also not sure why the last 2 rows are 89 with no child values.

Hi Nick, thanks for helping.

First, no real reason why the child values aren't pulled over. They could be. Second, no, that formula doesn't work. It gives a running total for all the rows, and I need totals for each parent.

I've attached another update, this time with all the children pulled over. (Edited to update attachment)

RE: Post #6. You're Welcome. By the way, since the AND function is removed the parentheses around A2=A3 could be removed i.e. =IF(A2=A3,B2,...
If after testing the formula you find it to be satisfactory, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post.
Let us know if you have any questions.

Try the following:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.