Need to make multiple subsets/subtotal of total values

[younas]

Hi,

I have a value set of 05 different items and want to make their subsets based on some criteria which i have mentioned in the attached sheet. Please help to make these sets using formulas preferably non-vba but vba will work too in case we have not non-vba solution. Thanks in advance for your great help as always.

Need to make sets of values with the no. of boxes of two sizes i.e. 200 and 100.
I have filled the Set values manually just to explain the required result
I have added 03 sets but they may increase

 

[AVK]

what is a criteria? how come values 200 - 100
explain detail.

 

[younas]

There are 02 sizes 200 and 100. If the total of values is 500 then there will be three sets i.e. 200, 200, 100. If the total is 80 then there will be only one set of 100 size.

 

[rahulshewale1]

Hii,

Please find the attached solution,,,
try this,hope this will help

IFERROR(IF(LOOKUP(SUM(F3:J3),{1;101},{1;2})=2,1,IF(LOOKUP(SUM(F3:J3),{1;101},{1;2})=1,"")),"")

Regard
rahul shewale

 

[bosco_yip]

Please see attached file.

Regards
Bosco

 

[younas]

Please see attached file.

Regards
Bosco

Hi Bosco,

Known values are from column "A" to "E". we need to calculate the values from column "F" to on-wards using formula.

Things we already know:

1. total units of each item

Things to calculate are

1. units for each set we make (yellow highlighted).
   
2. set boxes (green highlighted). You have done already

 

[younas]

I want to calculate each Set units (yellow highlighted) using the total units that I already have. I hope this will clarify my question.

 

[younas]

I think I have done it. Please review the attached and advise if everything is all right.