Search code examples
excelexcel-formulaparent-childhierarchyhierarchical-data

Summing the cells of children and assigning to the parent cell in a hierarchy

I have a data with several thousands of rows with parent-child hierarchy levels starting from 0 to 10, a sample is shown in the image below

sample hierarchy

Among the hierarchy levels that are important for me are 3 until 10. For me level 3 is parent and under this level are several parts and sub parts with different properties [column D] and Weight [column E]. I want to add the weights from column E based on a property type from column D and put the value in the respective parent cell.

For example considering the Range B10:E14 from the above image, I would like to check the weight of steel (sum if necessary) and put in the parent row of hierarchy level 3.

As a starting point I am trying to add a new column with the parent next to each part as shown below and add the weight and put in the parent field.

Is it possible to extract the parent name next to each part and add the weights in each hierarchy level 3 with an Excel formula? I am using Microsoft 365.

Solution

  • This looks more like a macro / VBA job. But if you really want to use formulas, then SUMIF is what you need. For instance put in cell F10 of your "basic idea"

    SUMIF(E11:E14;"steel";F11:F14)

    to have the total weight of steel from the subparts. If you put "steel" (or "wood") in another cell, say G1, then you may change your formula to

    SUMIF(E11:E14;$G$1;F11:F14)

    so changing the cell content will change all the sums

    As for reporting the parent name, one line of reasoning is: if this line is at level 4+ and the previous line is a level-3 parent then show its name; if it is not, then show the parent's name from the previous line. So e.g. in cell C11 you put

    IF(B11>3;IF(B10=3;D10;C10);"")

    and copy it to all cells in col C