Sum Values in a column based on three different columns

Question

This is more of a complex problem than what is stated in the title. I have attached two images with sample data to explain the problem better. In the first image, I am trying to report cost-roll up for 2021 and 2022 for a Program (Program 1). The issue is, this report depends on the jobs (Production) that will be completed in 2021 and 2022 for the same product. ABC is the parent product and BC goes into ABC to make it a final product. BC is made from B and C valued at $500 each. Hence, the total value of ABC is $1000 per qty. But if I use SUMIFS, it adds up all the values for Program 1 and 2021; Program 1 and 2022 giving me a value of $2000 per qty of ABC. I would like to ignore the cost values for BC from the table so that I don't have duplicate costs in my final report.

The logic I have in mind is to check if value in Column C is in Column B, if yes, sum all values in Column D for unique values in Column A. I need help converting this logic to a formula. My logic could be wrong though.

The second image shows the relationship between each part. I have similar data but in a much larger data set.

Please feel free to ask any questions if the above explanation doesn't make sense. Appreciate any help offered.

thanks in advance

VJ

Answer 1

One way I use to do these, instead of complicated SUMIF() is to extract the data in separate (hidden) columns.

For example, if I want the Costs column (D) when the Parent column (B) is ABC, then I can place something like this in Column H:

Title:  ABC Costs
Cells:  =IF(B2="ABC";D2;0)
        =IF(B3="ABC";D3;0)    # you don't have to edit each one
         ...                  # select first and empty cells under then hit Ctrl-D

Then I can simply get the sum of that new column (SUM(H1:H9)) and that's the total of the Costs for ABC.

Repeat for each thing you want to extract. Done.