Question

According to the constraints of BRAZIL BEANS, I have incorporated the following checks in meeting all their requirements, please see Sheet1.
“Each box ordered must contain 3 different types of beans”. In order to eliminate the error of selecting more than 3 types of beans, a pick list was designed in cells B3 to B5. Cells C3, D3 and E3 to Cells C5, D5 and E5 are also link to a Match function which selects the right Weight, Cost and Sale Price from “BRAZIL BEANS sheet” as this prevents human intervention which may leads to transposition errors when copying between sheets.
“Each box ordered must contain 20 bags of coffee or less”, in cell F9 I have incorporated a flag to identify when the number of bags exceeds 20.
“Each box ordered must weigh equal to or less than 150 ounces” ”, in cell I9 I have incorporated a flag to identify when the weight of the box exceeds 150 ounces.
“Each box ordered must contain between 4 and 8 bags of each different bean type” in cells F3 to F5, I have added a data validation to meet your constraint.
Formulas Used in the Calculations above

Profit per Bag = Sales Price - Cost
Sale Price
Cost
Profit per Bag

Special Dark Roast
9
(6)
3

Vanilla Hazelnut
7
(5)
2

Organic Special Roast
6
(5)
1

Total Profit = Number of Bags per Box X
Profit per Bag
Number of
Bags per Box
Profit per Bag
Total Profit

Special Dark Roast
4
3
12

Vanilla Hazelnut
4
2
8

Organic Special Roast
4
1
4

Total Weight = Weight (oz) X Number of Bags per Box
Weight (oz)
Number of
Bags per Box
Total Weight

Special Dark Roast
13
4
52

Vanilla Hazelnut
12.5
4
50

Organic Special Roast
10
4
40

Total Ordered Weight per Bag = sum of Ordered Weight Box
Total Weight

Special Dark Roast
52

Vanilla Hazelnut
50

Organic Special Roast
40

Total Ordered per Box Weight
142

Total Ordered Profit = sum of