This is similar to Worksheet 2, but now you have randomizing criteria for your conditions, which are in cells O11:O14. In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the condition(s) as specified, where X is the condition that changes every time you execute something in Excel. Your formula should work regardless of the conditions changing.

F M A D E H 1 N This is similar to Worksheet 2, but now you have randomizing criteria for your conditions, which are in cells 011.014. In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the condition(s) as specified, where X is the condition that changes every time you execute something in Excel. Your formula should work regardless of the conditions changing. 1 3 4 5 6 7 8 9 Gross Profit All products: Size X only: Material X and Product X only: Customer x for Quarter 3 only: 10 Conditions Criteria Customer: Size: Material: Product: Customer 13 L Gold Product 57 11 12 13 14 15 16 17 18 19 20 21 Date Salesperson Customer Product Material Size 1/1/19 SP 20 CUST-025 PROD 005 MATL Citrine SIZES 1/1/19 SP-17 CUST-021 PROD-OOS MATL-Aquamarine SIZE-S 1/1/19 SP-05 CUST-130 PROD-009 - MATL-Emerald SIZE-S 1/1/19 SP-08 CUST-019 PROD-012 MATL-Moonstone SIZE-L 1/1/19 SP-08 CUST-076 PROD-016 MATL-Cooper SIZE-L 1/1/19 SP-21 QUST-085 PROD-023 MATL-Jasper SIZE-S 1/1/19 SP-02 CUST-146 PROD 053 NATL Silver SIZE XS 1/1/19 SP-19 CUST-048 PROD-053 MATL-Silver SIZC-M 1/1/19 SP-12 CUST-053 PROD-OSS MATL-Amethyst SIZE-S - 1/1/19 SP-17 CUST-072 PROD-065 MATL-Moonstone SIZE-XL 1/1/19 SP-05 CJST-051 PROD-072 MATL-Garnet SIZE-XS 1/1/19 SP-12 CUST-123 PROD 073 MATL-Tanzanite SIZES 1/1/19 SP-07 CUST-012 - PROD 076 MATL-Ruby SIZE XL 1/1/19 SP-04 CUST-145 PROD-ONE MATL-Silve SIZE-S 1/1/19 SP-09 CUST-037 PROD-087 MATL-Quartz SIZE-S 1/1/19 SP-01 QUST-050 PROD-090 MATL-Silver SIZE-5 1/1/19 SP-15 CUST-128 PROD-093 MATL-Opal SIZE-XS 1/1/19 SP 16 CUST 145 PROD 097 MATL Opal SIZE XL 1/1/19 SP-13 CUST-078 PROD-103 MATL-Citrine SIZE- 1/1/19 SP-11 CUST-143 PROD-111 MATL-Quartz SIZE-M 1/1/19 SP-05 CUST-133 PROD-116 MATL-Opal SIZE-L 1/1/19 SP-16 OJST-026 PROD-138 MATL-Jasper SIZE-L 1/1/15 SP-04 CUST-146 PROD-144 MATL-Sapphire SIZE-XS 1/1/19 SP 19 CUST 001 PROD 149 MATL Emerald SIZE XS 1/1/19 SP-01 CUST-121 PROD-146 MATL-Bronze SIZE-XL 1/1/19 SP-02 CUST-003 PROD-148 MATL-Bronze SIZE-S 1/1/19 SP-15 CUST-007 PROD-158 MATL-Ruby SIZE-XS 1/1/19 SP-07 CJST-122 PROD-164 MATL-Emerald SIZE-M 1/1/19 SP-21 CJST-110 PROD-194 MATL-Garnet SIZE-M 1/2/19 SP 23 CUST-140 PROD 009 MATL Sapphire SIZE XL 1/2/19 SP-23 CUST-075 PROD-011 SIZE-S 1/2/19 SP-18 CUST-122 PROD-016 MATL-Ruby SIZE-M 1/2/19 SP-15 CUST-015 PROD-018 MATL-Topaz SIZE-XS Worksheet 4 Worksheets Worksheet 6 Worksheet 7 Worksheet 8 OPTION A Input Warehouse #Units MSA-13 100 $ MSA-42 B2S MSA-61 BBS MSA-5 89S MSA-16 3 $ MSA-74 48$ MSA-31 21 79 $ MSA-35 B5 S $ MSA-39 64 $ MSA-01 $ MSA-42 52 5 $ MSA-32 30 $ MSA-02 46 S MSA-57 22 S MSA-11 B6 $ MSA-10 76 5 MSA-49 53 $ MSA 62 71 $ MSA-44 46 $ MSA-32 22 $ MSA-18 12 $ MSA-20 5 MSA-36 8 $ MSA 35 92 $ MS4-03 $ MSA-70 15 $ MSA-08 $ MSA-OZ 39 5 MSA-98 $ MSA 07 54 $ MSA-65 20 S $ MSA-44 MSA-58 39 $ Worksheet 8 OPTION A Table Sales COGS Collections 524,020$ 373,708 $ 524,020 415,680 $ 286,510 $ 415,GAD 456,541 $ 254,174 $ 229 269 21.720 S 9,222 $ 21,720 35,134 24,258$ 35,134 53,816 $ 35,859 $ 53,816 870,232 $ 519,937 $ 870,232 352,612 $ 241,911$ 352,612 5,56BS 1,806 $ 5,56B 399.050 $ 248,241 $ 399,050 387,309 $ 298,717 $ 387,309 911,271 $ 704,058$ 911,271 3.204S 1,643$ 3,204 711,134 $ 452,503 $ 711,134 166.004S 121,7235 166,804 431,101$ 278,1775 431,104 287,850 $ 120,680$ 287,850 141.082 S 63,237 $ 141,082 91,372 $ 44,960 91,372 762,734 S 607,032 $ 762,734 242,465 $ 99,2865 242,465 305,712$ 169,770 $ 305,712 225,2549 143,700 S 225,254 151,280$ 70,374 151,280 501,440 $ 277,006 $ 501,940 238,556 $ 139,2B2$ 233,556 560,300 $ 443,7205 560,300 477,216 S 291,1755 177,216 899,040 $ 560,683 $ 899,040 235,996 $ 131,543 $ 235,996 231,570 $ 134,532 $ 231,570 739,530$ 376,253 $ 739,530 426,583 242,677 $ 416,583 Worksheet 8 OPTION B + 22 23 24 25 26 27 28 29 30 31 32 33 34 MATL-Capper 35 36 37 705 F M A D E H 1 N This is similar to Worksheet 2, but now you have randomizing criteria for your conditions, which are in cells 011.014. In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the condition(s) as specified, where X is the condition that changes every time you execute something in Excel. Your formula should work regardless of the conditions changing. 1 3 4 5 6 7 8 9 Gross Profit All products: Size X only: Material X and Product X only: Customer x for Quarter 3 only: 10 Conditions Criteria Customer: Size: Material: Product: Customer 13 L Gold Product 57 11 12 13 14 15 16 17 18 19 20 21 Date Salesperson Customer Product Material Size 1/1/19 SP 20 CUST-025 PROD 005 MATL Citrine SIZES 1/1/19 SP-17 CUST-021 PROD-OOS MATL-Aquamarine SIZE-S 1/1/19 SP-05 CUST-130 PROD-009 - MATL-Emerald SIZE-S 1/1/19 SP-08 CUST-019 PROD-012 MATL-Moonstone SIZE-L 1/1/19 SP-08 CUST-076 PROD-016 MATL-Cooper SIZE-L 1/1/19 SP-21 QUST-085 PROD-023 MATL-Jasper SIZE-S 1/1/19 SP-02 CUST-146 PROD 053 NATL Silver SIZE XS 1/1/19 SP-19 CUST-048 PROD-053 MATL-Silver SIZC-M 1/1/19 SP-12 CUST-053 PROD-OSS MATL-Amethyst SIZE-S - 1/1/19 SP-17 CUST-072 PROD-065 MATL-Moonstone SIZE-XL 1/1/19 SP-05 CJST-051 PROD-072 MATL-Garnet SIZE-XS 1/1/19 SP-12 CUST-123 PROD 073 MATL-Tanzanite SIZES 1/1/19 SP-07 CUST-012 - PROD 076 MATL-Ruby SIZE XL 1/1/19 SP-04 CUST-145 PROD-ONE MATL-Silve SIZE-S 1/1/19 SP-09 CUST-037 PROD-087 MATL-Quartz SIZE-S 1/1/19 SP-01 QUST-050 PROD-090 MATL-Silver SIZE-5 1/1/19 SP-15 CUST-128 PROD-093 MATL-Opal SIZE-XS 1/1/19 SP 16 CUST 145 PROD 097 MATL Opal SIZE XL 1/1/19 SP-13 CUST-078 PROD-103 MATL-Citrine SIZE- 1/1/19 SP-11 CUST-143 PROD-111 MATL-Quartz SIZE-M 1/1/19 SP-05 CUST-133 PROD-116 MATL-Opal SIZE-L 1/1/19 SP-16 OJST-026 PROD-138 MATL-Jasper SIZE-L 1/1/15 SP-04 CUST-146 PROD-144 MATL-Sapphire SIZE-XS 1/1/19 SP 19 CUST 001 PROD 149 MATL Emerald SIZE XS 1/1/19 SP-01 CUST-121 PROD-146 MATL-Bronze SIZE-XL 1/1/19 SP-02 CUST-003 PROD-148 MATL-Bronze SIZE-S 1/1/19 SP-15 CUST-007 PROD-158 MATL-Ruby SIZE-XS 1/1/19 SP-07 CJST-122 PROD-164 MATL-Emerald SIZE-M 1/1/19 SP-21 CJST-110 PROD-194 MATL-Garnet SIZE-M 1/2/19 SP 23 CUST-140 PROD 009 MATL Sapphire SIZE XL 1/2/19 SP-23 CUST-075 PROD-011 SIZE-S 1/2/19 SP-18 CUST-122 PROD-016 MATL-Ruby SIZE-M 1/2/19 SP-15 CUST-015 PROD-018 MATL-Topaz SIZE-XS Worksheet 4 Worksheets Worksheet 6 Worksheet 7 Worksheet 8 OPTION A Input Warehouse #Units MSA-13 100 $ MSA-42 B2S MSA-61 BBS MSA-5 89S MSA-16 3 $ MSA-74 48$ MSA-31 21 79 $ MSA-35 B5 S $ MSA-39 64 $ MSA-01 $ MSA-42 52 5 $ MSA-32 30 $ MSA-02 46 S MSA-57 22 S MSA-11 B6 $ MSA-10 76 5 MSA-49 53 $ MSA 62 71 $ MSA-44 46 $ MSA-32 22 $ MSA-18 12 $ MSA-20 5 MSA-36 8 $ MSA 35 92 $ MS4-03 $ MSA-70 15 $ MSA-08 $ MSA-OZ 39 5 MSA-98 $ MSA 07 54 $ MSA-65 20 S $ MSA-44 MSA-58 39 $ Worksheet 8 OPTION A Table Sales COGS Collections 524,020$ 373,708 $ 524,020 415,680 $ 286,510 $ 415,GAD 456,541 $ 254,174 $ 229 269 21.720 S 9,222 $ 21,720 35,134 24,258$ 35,134 53,816 $ 35,859 $ 53,816 870,232 $ 519,937 $ 870,232 352,612 $ 241,911$ 352,612 5,56BS 1,806 $ 5,56B 399.050 $ 248,241 $ 399,050 387,309 $ 298,717 $ 387,309 911,271 $ 704,058$ 911,271 3.204S 1,643$ 3,204 711,134 $ 452,503 $ 711,134 166.004S 121,7235 166,804 431,101$ 278,1775 431,104 287,850 $ 120,680$ 287,850 141.082 S 63,237 $ 141,082 91,372 $ 44,960 91,372 762,734 S 607,032 $ 762,734 242,465 $ 99,2865 242,465 305,712$ 169,770 $ 305,712 225,2549 143,700 S 225,254 151,280$ 70,374 151,280 501,440 $ 277,006 $ 501,940 238,556 $ 139,2B2$ 233,556 560,300 $ 443,7205 560,300 477,216 S 291,1755 177,216 899,040 $ 560,683 $ 899,040 235,996 $ 131,543 $ 235,996 231,570 $ 134,532 $ 231,570 739,530$ 376,253 $ 739,530 426,583 242,677 $ 416,583 Worksheet 8 OPTION B + 22 23 24 25 26 27 28 29 30 31 32 33 34 MATL-Capper 35 36 37 705