I have a list of PN's which is broken down into sub-assemblies. Each column represents a level of the assembly.

w/o formulas:

In the first picture, you have the values arranged, B is a sub-assembly of A, C is a sub-assembly of B. B is the sum of all its sub-assemblies C, and A is the sum of assemblies B (not C because they were already added to B).

Formulas

Formulas shown, but my question is: is there a way to automate this? Having A only sum values of its subassemblies up until another A is found, same with B. I have a list of almost 5000 values, I can't do it manually but it will take long hours

CodePudding user response：

Use a helper column to store the values for the C cells. Then you can use the following formula:

=IF(C2<>"",D2,IF(B2<>"",SUMIFS($D3:$D$17,$C3:$C$17,"<>")-SUMIFS($E3:$E$17,$B3:$B$17,"<>"),SUMIFS($E3:$E$17,$B3:$B$17,"<>")-SUMIFS($E3:$E$17,$A3:$A$17,"<>")))

Note what is and what is not absolute. The key is that we need to look at the values below the one in which the formula is placed and that will change dynamically.

Also note that this will not give correct results until the formula is dragged/filled down the entire dataset.

With Formula: