How to find the true demand when sellin products that are sold individually and in package

Hello!

I am trying to figure out how to find the demand on SKU level of components that are sold either by themselves or as components in package products. The components can be part of more than one package. What i have is a sales report telling how many of each components and each package that have been sold. I also have a conversion/BoM table stating which components that are included in the packages.

These two should be used to generate a third table stating the demand on sku level (units of sold components by themselves + number of packages where the component is included * number included in the different packages). I was hoping to be able to generate this without having a predefined list of products in the third table, instead i want it to be generated somehow so that whenever i add anything to the sales report and/or the conversion/BoM table, like adding a sku to a package, it will be updated automatically or whenever i refresh it.

In the example file i added some data in the material demand sheet, but best case scenario would be to only have headings that are filled in automatically.

So i guess it is two questions:

How do i get the total demand on SKU level in the most efficient way?

Can it be automated so i dont have to fill in any data in the third sheet? (i understand there must be something, but was thinking of somekind of formula that checks sheet one, adds all articles and then calculate the total demand).

how would you approach this problem?

Edit:

Added the way i solved it for now, works but needs alot of work everytime i update with new products etc, also all the logical operations makes the file super slow (not the small one in the example)

Is this any better?
Bom F2, copied down:
=SUMPRODUCT((Sales!$B$3:$B$100=BoM!B3)*(Sales!$C$3:$C$100)*BoM!E3)

MD, C6 copied down:
=SUMIF(BoM!D:D,'Material demand'!B6,BoM!F:F)+SUMIF(Sales!B:B,B6,Sales!C:C)