Excel Problem - Sum Total No. of Containers

Question

I'd like to seek advice on the following problem I'm facing in Excel (see below link).

Basically, I'm trying to auto calculate calculate the total no. of containers shipper per year (column M), based on the following criteria:

Column E - Whether goods are shipped together (same number) or separately (different number) Column F - No. of times goods are shipped out per year Column K - % of a container each good will occupy when shipped

For items shipped together, I need to develop a formula which will automatically calculate the how many containers will be shipped in a year, on this principle:

a. Taking the number of times all items shipped together i.e.

Item A + B + C will be shipped together 4 times a year, occupying 179% of a container. This will mean I need to ship out the goods in 2 x containers 4 times a year

b. Taking the no. of times items the next two items are shipped together i.e.

Item A + C will be shipped out together 6 times a year, occupying 50% of a container. This will mean I need to ship out the goods in 1 x containers 6 times a year

c. Remaining no. of times the last item is shipped out i.e.

Item A will be shipped out the remaining 2 times (12 - 6 - 4), occupying 30% of a container. This will mean I need to ship out 1 x container 2 times a year

Hence total no. of containers shipped for items A + B + C is 16 times a year, and this should appear in column M for all three items.

The main challenge that I have is that this list is dynamic and column E will change every time we evaluate new products i.e. in this example, we have 3 items shipped together, but for another instance there may be 5 items shipped together

Hence any formula developed has to be able to factor in this dynamism. I've not been able to solve this issue, and will be appreciative for any advice on this.

Thank you!

Sample Table For Problem