Excel: Adding with Dynamic Ranges

Hi All, Hoping someone can help me figure this one out. Ideally, I would like to do this with formulas so others who use the spreadsheet can understand how it works. If not possible then VBA will be good. I will try to explain the best I can. We have multiple assemblies with sub assemblies, some have up to 9 sub assemblies. Each given a level number, so all the assembly 3's below a 2 make up assembly 2, and all the 4 assemblies below assembly 3 make up that one, so on and so on. I cant just add up all the 3's because they will be different depending on the parent assembly 2, and this is true for every level What I need to do is all all the totals of the very bottom level assembly, put that total into its parent assembly, and continue this until we get to one. The reason we have to do it this was is because the very bottom level assembly parts will have the correct weight on them, but the parent assembly may have an estimated weight that is incorrect. We have to assume that the children are always correct. Below I have just chucked some random numbers in and manually put in what the weight should be showing in the Calculated Weight Column. I really hope I have made sense and someone can help me. Thanks.

Assembly Total Mass Calculated Weight
2 1.00 62 ( 30 + 32)
3 2.00 30 (From 4 below)
4 3.00 30 (4 + 5 + 6 + 7 + 8 + 9)
5 4.00
5 5.00
5 6.00
5 7.00
5 8.00
3 9.00 32 (10 + 11 + 12)
4 10.00
4 11.00
4 12.00

This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of March 06, 2017.

For more resources for Microsoft Excel