2. Go to the All Locations worksheet, where Benicio wants to summarize the quarterly and annual totals from the three locations for each type of product. Consolidate the sales data from the three locations as follows: a. In cell B5, enter a formula using the SUM function and 3-D references that totals the Mini sales values (cell B5) in Quarter 1 from the U.S., Canada, and Mexico worksheets. b. Fill the range C5:E5 with the formula in cell B5 to total the Mini sales for Quarters 2-4. c. Fill the range B6:E7 with the formulas in the range B5:E5 to total the sales for the other products in Quarters 1-4. Excel File Edit View Insert Format Tools AutoSave CIT Data Window Help 2 NP.EX19 CS5-8a Emily Sullivan 2 Review View Developer Tell me Home Insert Draw Page Layout Formulas Data Get Add-ins My Add-ins H?. Pivot Tabla Recommended Table Pivot Tables Data from Pictura Pictures Shapes Icons Recommended Charts Tables Data From Pieture lustrations Add-ins Charts B5 B C D E F G H G 1 01 2021 Sales: All Locations Q3 04 Q2 Total 2020 Total Country U.S. Canada Mexico Ta NO Vil $ $ $ $ Management 3 4 Type 5 Mini 6 Voice-activated 7 Waterproof 8 Total $ 9 10 in $000 11 12 Performance 13 14 Revenue Amts $ 15 Rating 16 17 18 19 20 21 5,700 $ 5,800 $ 5,900 $ 6,000 $ 6.100 Poor Fair Good Very good Fantastic 2 Documentation U.S Canada Mexico All Locations Current Sales Ready Sales Projections Suppliers Home Insert Draw Page Layout Formulas Data Review View Developer Tell 13 Get Add-ins Pivot Table Recommended Table Pivot Tables Data from Picture Pictures Shapes Icons My Add-ins Rec Tables Data From Picture illustrations Add-ins D22 x fx A B C D F G H K 4 Type 1 2 3 2021 Sales: United States 01 Q2 03 04 5 Mini $ 1,021 $ 1,025 $1,076 $ 1,027 $ 6 Voice-activater 987 990 1,015 1,016 $ 7 Waterproof 815 826 834 859 $ 8 Total $ 2,823 $ 2,841 $ 2,925 $ 2,902 $ 9 10 In $000 Total (6) (29) (44) (79) 12 13 14 16 17 18 19 20 23 25 25 27 29 30 32 83 35 36 U.S. Documentation Ready 17 Canada Mexico All Locations Current Salos Sales AutoSavo NP EX1 BESUS Page Layout Formulas Home Insert Draw Data Review View Developer Tel Get Add-Ins Pivot Table Recommended Table Plvot Tables Data from Picture Pictures Shapes Icons My Add-ins A6 Rec Tables Data From Picture illustrations Add-ins FS X fx =SUM(B5-ES) A B c D E G H K 4 type 1 2 3 2021 Sales: Canada 01 02 93 04 5 Mini $ 604 $ 609 $ 601 $ 602 $ 6 Voice-activated 497 495 502 523 | $ 7 Waterproof 429 423 431 435 1 $ 8 Total $ 1,530 $ 1,527 $ 1.634 $ 1.560 $ Total (26) (30) 10 in 9000 11 12 13 14 15 16 123t in to f' a g888888888 21 Documentation U.S. Canada Mexico Roady All Locations Current Sales Excel Fle EO VIE NP EX19 CS5 AutoSave Insert Home Draw Page Layout Formulas Data Review View Developer Tell me Ett Got Add-ins Pivot Tabla Recommended Table Pivot Tables Data from Picture Pictures Shapes Icons My Add-ins Recommar Charts Tables Data From Picture Illustrations Add-ins G15 x V fx A B c D E G H 1 2 3 4 Type 5 Mini 6 Voice activate 7 Waterproof 8 Total 9 10 in 5000 2021 Sales Mexico Q1 Q2 Q3 04 Total $ 741 $ 740 $ 744 $ 750 $ 19 328 312 327 324 $ 4 504 506 499 503 $ 1 $ 1,573 $ 1,558 $ 1,570 $ 1,577 $ (4 15 16 + 18 19 & 21 24 25 26 27 28 30 31 35 Documentation Ready 17 US Canada Mexico All Locations Current Sales Sales Projed
