Click in cell I4, note that you can choose a product from the list. The next three calculations must return the correct values for the selected product. In K4 provide a calculation to count how many different part codes are required for the product selected in I4.

In J7:J9 create a formula to calculate how many different part codes are required for the product selected in I4 from each supplier.

In K7:K9 create a formula to add the quantity of parts required for the product selected in I4 from each supplier.

E F G H Bill of Materials w Product Part Code Quantity Product Description V Price Each Supplier Total Price JB Compact Products JB Master PX1004683 Block Rear 14-X $12.64 Smithsons $12.64 JB Master PX242979 Spring 5b $2.16 Smithsons $8.64 Supplier Products Parts Needed JB Master PX373195 Latch $2.61 Smithsons $2.61 H&G 12 JB Master PX374089 Bracket 552 $48.86 Smithsons $48.86 Smithsons 17 JB Master Axle $2.99 Smithsons $2.99 Parts Plus 11 PX37 4561 JB Master PX577199 8 Nut Super $1.66 Smithsons $13.28 JB Master PX723206 Large Foot $1.83 Parts Plus $7.32 12 JB Master PX723541 Arm 320 $1.91 Smithsons $1.91 JB Compact PX723542 $2.59 - Arm 35 $2.59 Parts Plus JB Master PX723557 Foot $12.44 A $3.11 Parts Plus JB Compad PX723566 Pin 10 $2.09 H&G $2.09 JB Master PX723593 Bolt $1.91 H&G $15.28 00 PX723594 Large Bolt $4.18 JB Compad IN $2.09 H&G 18 JB Compad PX723596 A Small Bolt $2.59 H&G $10.36 19 JB Master P X7237 42 Socket $1.99 Smithsons $1.99 20 JB Compad PX723743 Socket $2.29 Parts Plus $4.58 JB Master PX7237 45 $10.44 AAN Leg $2.61 Parts Plus 22 JB Compact PX723820 Foot Sq $2.19 Parts Plus $8.76 23 JB Compad PX725125 Nut $2.49 H&G $9.96 24 JB Master PX792653 00 Rivet Use: 1004688-16 $2.08 Smithsons $16.64 JB Compad PX796490 Nut Wing $1.99 Parts Plus $7.96 26 JB Master PX797516 $4.16 N Spring 3a $2.08 H&G JB Master PX807530 Grip $3.11 Smithsons $3.11 N- 28 JB Master PX807531 Pin 12 $1.74 Smithsons $3.48 JB Master PX807532 Pin 33 $1.74 Smithsons $3.48 NN 30 JB Compad PX807539 Vice Screw Sa $21.24 H&G $42.48 JB Master PX807540 Vice Screw Sa $4.00 H&G $8.00 00 N 32 JB Master PX809486 Washer Special $1.99 H&G $15.92 33 JB Master PX820261 Block 12/A $14.29 Smithsons $ 14.29 JB Compad PX820262 1 Block Front $12.09 Parts Plus $12.09 35 JB Compad PX820263 2 RH Nut $1.99 Parts Plus $3.98 PX820266 Block Rear $10.99 Parts Plus $ 10.99 JB Compad 37 JB Master PX820728 Knob $1.99 Smithsons $3.98 NN 38 JB Compact PX820728 Knob $1.99 Smithsons $3.98 39 JB Compad PX820834 Single Grip $2.49 Parts Plus $2.49 JB Master PX823385 Screw 2.4mm $2.16 H&G $8.64 41 JB Master PX823386 00 Screw Special $1.83 H&G $14.64 42 JB Master PX823978 1 Vice Jaw Front $26.24 Smithsons $26.24 43 JB Compact PX823978 Vice Jaw Front $26.24 Smithsons $26.24 44 JB Compad PX860085 2 Screw 1.2mm $2.09 H&G $4.18