May 3 Product car plane train car Month Jan Feb 128 75 124 135 138 95 104 111 89 Mar 50 90 146 84 88 60 74 115 106 Apr 107 139 130 127 92 113 84 146 82 86 90 117 61 63 87 126 116 99 Jun 54 146 144 102 119 138 125 106 86 Jul 86 88 111 109 60 119 110 71 111 89 133 122 127 92 134 77 91 114 car plane car train car 10 11 12 13 14 15 16 17 18 19 20 21 22 23 STEPS Product Total Month feb car SUMIF(B8:B16,C21,D8:16) 24 1. SUMIR 25 26 2A. MATCH 27 28.OFFSET 28 2C. OFFSET & MATCH 29 20. SUMIF,OFFSET & MATCH 30 Each row of the file Assignment 4.xlsx contains sales data for a product (car, train, or plane) from January through July. Suppose you enter a month and a product into the worksheet. Write a formula that gives the total sales of the product during the given month in cell E21. Month Jan 128 Feb Mar Jun Jul 86 Product car plane train car 75 50 90 146 84 88 60 74 124 135 138 95 104 111 89 Apr May 86 54 90 146 117 144 61 102 63 119 87 138 126 125 116 106 99 86 107 139 130 127 92 113 84 146 82 car 89 133 122 127 92 134 88 111 109 60 119 110 71 111 plane car train 115 77 91 114 car 106 Step 1 (cell B24). Use SUMIF to compute the total sales for the product 'car' for the month of February'. The formula I have used in cell B24 is SUMIF (88:B16, C21, 08:016) Notice in this formula, I have chosen the sum range Feb column-myself rather than have excel choose that for me. I would like to use OFFSET and MATCH functions to do this for me. Step 2 A. (cell B26) We need to use excel to choose the array 08:016 in the SUMIF formula above. We can first start with using the MATCH formula to identify the position of the month specified in cell D21. B. (cell B27) Second, we will use the OFFSET function so that we can start with reference cell B7 and arrive at cell 08. We will reference the output from the MATCH function above to tell excel how many columns to move from cell B7 C. (cell B28) Now that we have set up the OFFSET formula, we will make it a nested formula such that it need not rely on the MATCH formula from Step 2A. So, we will copy the MATCH formula and paste it within the OFFSET formula against the appropriate field. D. (cell B29) The last step is to use the SUMIF formula as in Step 1 but we will replace the last argument (08:016) with the OFFSET formula from Step 2C. Another change we need to make here is to change the height in the OFFSET formula to 9 (it was 1 in Step 2C). Changing the height takes into account all the rows in that column. E. (cell E21) Now that we have the final formula ready, we can paste that in cell E21 (highlighted cell). Make sure when you change the product or month in cells C21 or 021, the final output in the highlighted cell updates as well
