Multilevel Bill of Material cost sum

Hello, I'm working on tool to estimate Bill of Material Cost. As a input I'll have multilevel Bill of Material with cost assigned to each position and I need to have multivelel summing to be able to verify each level cost(each of postion cost is some of below levels cost). Sometimes I'll have 10 postions and sometimes 200 lines.

Please find attached sample. Thank you in advance for any support.

the example is confusing as Col D contains both Leaf Cost and {expected?} Node Sub Totals

if you removed the node totals from Col D (e.g. X1, X2, X8 etc) such that you only stored Leaf cost we could replicate your Col D results, in another column, using:

Formula:

K3:

=SUM(D3:INDEX(D:D;ROW(D3)+MATCH(TRUE;INDEX(N(+A4:A$29)<=A3;0);0)-1))

copied down

which would replicate the expected results.

however, if, as I suspect, this isn't what you need, I can only suggest posting a sample that better illustrates where the leaf-costs are stored (e.g. X3)

First of all, thank you for your answer. I was not specific enough, I attached my input and based on this input I need to build file which will recalculate all levels of Bill of Material if I'll change any cost manualy.

hi, the point still stands however, you would only be changing the cost of leaf-values, not nodes.

if you store only the leaf-costs in Col D, and use the above formula in Col K, you change any leaf-cost value in D and the subtotals in Col K will update automatically.

if you need to store all values (leaf costs & node totals) in Col D, you will need to use VBA to a) ensure only leaf-level values are changed by end-user (never Node), and b) subsequently recalculate, and overwrite, previous node subtotals.