On the Inventory worksheet in cell D3, create a formula to determine the total quantity sold within the category in B1. Enter a SUMIF function nested in an IF function to determine id B3 has value. If B3 has value, then you will sum the Trans_Sold if the Trans_Item meets the criteria in B3. If B3 does not have value, the the cell is left blank.
AutoSave OFF HESU 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) V 11 Insert v X La . Ai = = Number LI AYO WE 5 Paste 00 B I U A ' $ % ) - Ideas V DX Delete F Format Sensitivity V Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select CZ 4 x fc =DAVERAGE(InventoryTable,C5,D1:E2) A B D E F G H 1 J j K L M N N 0 0 o R S T U Trans_Item Trans Qty Trans_Sold Trans Delivered 1 Transaction 2 Trans Category Food WeekDay Fri 3 4 5 6 Sum Average Trans Sold 25 8 7 8 Trans Sold 9 10 Transaction 11 12 13 Trans Delivered 14 5 WeekDay Mon Mon Wed Thur Fri 14 15 16 17 18 Trans_Item 1 A1 2 A3 3 AS 4 A3 5 A6 6 A3 7 A1 8 A2 9A7 10A19 11 A1 12 A10 13 A1 14 AS 7 14 15 16 7 10 Fri 19 4 2 15 Trans Qty Trans_Category -14 Food 5 Food -7 Golf -14 Food -15 Massage -16 Food -7 Food -10 Clothing 4 Clothing -2 Golf 15 Food -18 Massage -6 Food -6 Clothing -13 Clothing -2 Food -17 Food -8 Clothing 5 Golf -11 Novelty -4 Reading O Golf -18 Golf 6 Clothing 4 Novelty Daily Transactions + 20 21 22 23 24 25 26 27 18 6 6 15 A2 13 Fri Fri Sat Sun Thur Mon Wed Thur Fri Fri Sat Sat Mon Mon Mon Tues Wed Wed Thur 2 17 8 5 16 A1 17|A3 18 A15 19 A13 20 A14 21 A4 22 A9 23 AS 24 A15 25 A17 InventoryAudit 28 29 30 31 32 33 34 35 11 4 18 6 4 + 100% AutoSave OFF BESU- 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X Calibri (Body) V 11 AA Insert v = = General LI WE AYO 5 Paste .00 A $ % ) THI Ideas DX Delete F Format Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select Sensitivity D3 4 x fx A B D E F G H 1 1 L M M N 0 P 0 R Projection Actual 1 Category Massage 2 Category Item 3 1 A9 4 2 A10 5 3 A11 6 4 A12 4 5 6 6 6 7 5 8 6 9 10 11 12 13 14 7 7 8 9 10 11 12 13 15 14 15 Mon Tues Wed Thur Fri Sat Sun 16 17 18 19 20 21 22 23 24 25 26 27 Categories Massage Golf Food Novelty Reading Clothing 28 29 30 Item Total out Total in Ending Actual Max Stock Turnover Description Beginning inventory 31 32 33 34 A1 AZ 3 35 331 48 Inventory Discrepancy 25 On Target 20 On Target 15 On Target 0.00% Healthy Trail Mix 0.00% Resort Polo 0.00% Healthy Energy Bar InventoryAudit Daily Transactions + + 100% AutoSave OFF HESU 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) V 11 Insert v X La . Ai = = Number LI AYO WE 5 Paste 00 B I U A ' $ % ) - Ideas V DX Delete F Format Sensitivity V Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select CZ 4 x fc =DAVERAGE(InventoryTable,C5,D1:E2) A B D E F G H 1 J j K L M N N 0 0 o R S T U Trans_Item Trans Qty Trans_Sold Trans Delivered 1 Transaction 2 Trans Category Food WeekDay Fri 3 4 5 6 Sum Average Trans Sold 25 8 7 8 Trans Sold 9 10 Transaction 11 12 13 Trans Delivered 14 5 WeekDay Mon Mon Wed Thur Fri 14 15 16 17 18 Trans_Item 1 A1 2 A3 3 AS 4 A3 5 A6 6 A3 7 A1 8 A2 9A7 10A19 11 A1 12 A10 13 A1 14 AS 7 14 15 16 7 10 Fri 19 4 2 15 Trans Qty Trans_Category -14 Food 5 Food -7 Golf -14 Food -15 Massage -16 Food -7 Food -10 Clothing 4 Clothing -2 Golf 15 Food -18 Massage -6 Food -6 Clothing -13 Clothing -2 Food -17 Food -8 Clothing 5 Golf -11 Novelty -4 Reading O Golf -18 Golf 6 Clothing 4 Novelty Daily Transactions + 20 21 22 23 24 25 26 27 18 6 6 15 A2 13 Fri Fri Sat Sun Thur Mon Wed Thur Fri Fri Sat Sat Mon Mon Mon Tues Wed Wed Thur 2 17 8 5 16 A1 17|A3 18 A15 19 A13 20 A14 21 A4 22 A9 23 AS 24 A15 25 A17 InventoryAudit 28 29 30 31 32 33 34 35 11 4 18 6 4 + 100% AutoSave OFF BESU- 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Insert v Calibri (Body) V 11 X LO = = " General LI WE AYO 5 DX Delete v Paste Av & Ideas $ %) V Sensitivity .00 0 Conditional Format Formatting as Table Cell Styles Format Sort & Filter Find & Select R23 4 x fx E H M N P 9 R 5 y w Y Projection Projection Projection Projection Projection Projection Golf Item A 16 A17 A18 A19 Food Item A1 A3 A20 Novelty Item A 13 A14 A15 Reading Item AS A6 Clothing item AZ A4 AS B 1 Massage 2 item 3 AS 4 A10 5 A11 6 A12 7 8 9 10 11 12 13 14 15 16 17 18 Item Description 19 A1 Healthy Trail Mix 20 A2 Resort Polo 21 A3 A3 Healthy Energy Bar 22 A4 Spirits Lifted Through Yoga 23 AS Golf Tees 24 A6 25 AZ 26 AS 27 A9 28 A 10 29 Ali 30 A12 RushReOrderPoint Max_On_Hand Wholesell Price Sale Price Category 451 $ 15.99 $ 21.99 Food 351 $ 29.48$ 45 34 Clothing 25$ 3.90$ 9.95 Food 15$ 6.90$ 14 95 Reading sol $ 30$ 259 Golf Delivery Time Current Inventory 10 14 4 ReOrder_Point 23 15 5 20 17 15 15 Discontinue 10 N 10 N 10 N 101Y 10 N ReorderStatus Vendor OK Vendor 1 Reorder Vendor 2 Rush Vendor 1 OK Vendor 4 OK Vendor 11 27 0 31 A13 32 A14 33 A15 34 A 16 35 A17 36 A18 37 A19 38 A2O 39 40 41 42 43 44 45 47 48 Inventory InventoryAudit Daily Transactions + a + 75% AutoSave OFF HESU 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) V 11 Insert v X La . Ai = = Number LI AYO WE 5 Paste 00 B I U A ' $ % ) - Ideas V DX Delete F Format Sensitivity V Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select CZ 4 x fc =DAVERAGE(InventoryTable,C5,D1:E2) A B D E F G H 1 J j K L M N N 0 0 o R S T U Trans_Item Trans Qty Trans_Sold Trans Delivered 1 Transaction 2 Trans Category Food WeekDay Fri 3 4 5 6 Sum Average Trans Sold 25 8 7 8 Trans Sold 9 10 Transaction 11 12 13 Trans Delivered 14 5 WeekDay Mon Mon Wed Thur Fri 14 15 16 17 18 Trans_Item 1 A1 2 A3 3 AS 4 A3 5 A6 6 A3 7 A1 8 A2 9A7 10A19 11 A1 12 A10 13 A1 14 AS 7 14 15 16 7 10 Fri 19 4 2 15 Trans Qty Trans_Category -14 Food 5 Food -7 Golf -14 Food -15 Massage -16 Food -7 Food -10 Clothing 4 Clothing -2 Golf 15 Food -18 Massage -6 Food -6 Clothing -13 Clothing -2 Food -17 Food -8 Clothing 5 Golf -11 Novelty -4 Reading O Golf -18 Golf 6 Clothing 4 Novelty Daily Transactions + 20 21 22 23 24 25 26 27 18 6 6 15 A2 13 Fri Fri Sat Sun Thur Mon Wed Thur Fri Fri Sat Sat Mon Mon Mon Tues Wed Wed Thur 2 17 8 5 16 A1 17|A3 18 A15 19 A13 20 A14 21 A4 22 A9 23 AS 24 A15 25 A17 InventoryAudit 28 29 30 31 32 33 34 35 11 4 18 6 4 + 100% AutoSave OFF BESU- 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments X Calibri (Body) V 11 AA Insert v = = General LI WE AYO 5 Paste .00 A $ % ) THI Ideas DX Delete F Format Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select Sensitivity D3 4 x fx A B D E F G H 1 1 L M M N 0 P 0 R Projection Actual 1 Category Massage 2 Category Item 3 1 A9 4 2 A10 5 3 A11 6 4 A12 4 5 6 6 6 7 5 8 6 9 10 11 12 13 14 7 7 8 9 10 11 12 13 15 14 15 Mon Tues Wed Thur Fri Sat Sun 16 17 18 19 20 21 22 23 24 25 26 27 Categories Massage Golf Food Novelty Reading Clothing 28 29 30 Item Total out Total in Ending Actual Max Stock Turnover Description Beginning inventory 31 32 33 34 A1 AZ 3 35 331 48 Inventory Discrepancy 25 On Target 20 On Target 15 On Target 0.00% Healthy Trail Mix 0.00% Resort Polo 0.00% Healthy Energy Bar InventoryAudit Daily Transactions + + 100% AutoSave OFF HESU 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) V 11 Insert v X La . Ai = = Number LI AYO WE 5 Paste 00 B I U A ' $ % ) - Ideas V DX Delete F Format Sensitivity V Conditional Format Formatting as Table Cell Styles Sort & Filter Find & Select CZ 4 x fc =DAVERAGE(InventoryTable,C5,D1:E2) A B D E F G H 1 J j K L M N N 0 0 o R S T U Trans_Item Trans Qty Trans_Sold Trans Delivered 1 Transaction 2 Trans Category Food WeekDay Fri 3 4 5 6 Sum Average Trans Sold 25 8 7 8 Trans Sold 9 10 Transaction 11 12 13 Trans Delivered 14 5 WeekDay Mon Mon Wed Thur Fri 14 15 16 17 18 Trans_Item 1 A1 2 A3 3 AS 4 A3 5 A6 6 A3 7 A1 8 A2 9A7 10A19 11 A1 12 A10 13 A1 14 AS 7 14 15 16 7 10 Fri 19 4 2 15 Trans Qty Trans_Category -14 Food 5 Food -7 Golf -14 Food -15 Massage -16 Food -7 Food -10 Clothing 4 Clothing -2 Golf 15 Food -18 Massage -6 Food -6 Clothing -13 Clothing -2 Food -17 Food -8 Clothing 5 Golf -11 Novelty -4 Reading O Golf -18 Golf 6 Clothing 4 Novelty Daily Transactions + 20 21 22 23 24 25 26 27 18 6 6 15 A2 13 Fri Fri Sat Sun Thur Mon Wed Thur Fri Fri Sat Sat Mon Mon Mon Tues Wed Wed Thur 2 17 8 5 16 A1 17|A3 18 A15 19 A13 20 A14 21 A4 22 A9 23 AS 24 A15 25 A17 InventoryAudit 28 29 30 31 32 33 34 35 11 4 18 6 4 + 100% AutoSave OFF BESU- 1e03_grader_h1_Inventory Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Insert v Calibri (Body) V 11 X LO = = " General LI WE AYO 5 DX Delete v Paste Av & Ideas $ %) V Sensitivity .00 0 Conditional Format Formatting as Table Cell Styles Format Sort & Filter Find & Select R23 4 x fx E H M N P 9 R 5 y w Y Projection Projection Projection Projection Projection Projection Golf Item A 16 A17 A18 A19 Food Item A1 A3 A20 Novelty Item A 13 A14 A15 Reading Item AS A6 Clothing item AZ A4 AS B 1 Massage 2 item 3 AS 4 A10 5 A11 6 A12 7 8 9 10 11 12 13 14 15 16 17 18 Item Description 19 A1 Healthy Trail Mix 20 A2 Resort Polo 21 A3 A3 Healthy Energy Bar 22 A4 Spirits Lifted Through Yoga 23 AS Golf Tees 24 A6 25 AZ 26 AS 27 A9 28 A 10 29 Ali 30 A12 RushReOrderPoint Max_On_Hand Wholesell Price Sale Price Category 451 $ 15.99 $ 21.99 Food 351 $ 29.48$ 45 34 Clothing 25$ 3.90$ 9.95 Food 15$ 6.90$ 14 95 Reading sol $ 30$ 259 Golf Delivery Time Current Inventory 10 14 4 ReOrder_Point 23 15 5 20 17 15 15 Discontinue 10 N 10 N 10 N 101Y 10 N ReorderStatus Vendor OK Vendor 1 Reorder Vendor 2 Rush Vendor 1 OK Vendor 4 OK Vendor 11 27 0 31 A13 32 A14 33 A15 34 A 16 35 A17 36 A18 37 A19 38 A2O 39 40 41 42 43 44 45 47 48 Inventory InventoryAudit Daily Transactions + a + 75%
