8. Camille also wants to summarize the number of discounted products by product type and calculate their total and average discounted prices. In cell K2, enter a formula using the COUNTIF function that counts the number of Carrier products, using Discounts[Product Type] as the range and cell J2 as the criteria. Fill the range K3:K9 with the formula in cell K2.| In cell L2, enter a formula using the SUMIF function that totals the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the sum_range. Fill the range L3:L9 with the formula in cell L2. In cell M2, enter a formula using the AVERAGEIF function that averages the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the average_range. Fill the range M3:M9 with the formula in cell M2. 9. Go to the October Ads worksheet, which lists all the digital ads released in October. Sort the data in the table in ascending order first by channel and then by release date. 10. Camille also wants to calculate ad amount subtotals for each channel. Convert the table to a range. Insert a subtotal at each change in the Channel value, using the Sum function to calculate the subtotals. Add subtotals to the Ad Amt values only, and include a summary below the data. Collapse the outline to display only the subtotals for each channel and the grand total. 11. Go to the Ad Lookup worksheet, which lists all the ads released in October and planned for November. Camille wants to look up the release date of each ad. In cell H3, enter a formula using the INDEX function. Use a structured reference to the AdLookup table (range A2:E36) as the array. For the row_num argument, use the MATCH function. Use the Ad ID (cell H2) as the lookup_value for the MATCH function. Use a structured reference to the ID column (range A2:A36) as the lookup_array for the MATCH function. Specify an exact match (0) for the MATCH function. Use the Release Date column number (column 4) as the column_num for the INDEX function. 8. Camille also wants to summarize the number of discounted products by product type and calculate their total and average discounted prices. In cell K2, enter a formula using the COUNTIF function that counts the number of Carrier products, using Discounts[Product Type] as the range and cell J2 as the criteria. Fill the range K3:K9 with the formula in cell K2.| In cell L2, enter a formula using the SUMIF function that totals the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the sum_range. Fill the range L3:L9 with the formula in cell L2. In cell M2, enter a formula using the AVERAGEIF function that averages the discount prices for Carrier products, using Discounts[Product Type] as the range, cell J2 as the criteria, and Discounts[Discount Price] as the average_range. Fill the range M3:M9 with the formula in cell M2. 9. Go to the October Ads worksheet, which lists all the digital ads released in October. Sort the data in the table in ascending order first by channel and then by release date. 10. Camille also wants to calculate ad amount subtotals for each channel. Convert the table to a range. Insert a subtotal at each change in the Channel value, using the Sum function to calculate the subtotals. Add subtotals to the Ad Amt values only, and include a summary below the data. Collapse the outline to display only the subtotals for each channel and the grand total. 11. Go to the Ad Lookup worksheet, which lists all the ads released in October and planned for November. Camille wants to look up the release date of each ad. In cell H3, enter a formula using the INDEX function. Use a structured reference to the AdLookup table (range A2:E36) as the array. For the row_num argument, use the MATCH function. Use the Ad ID (cell H2) as the lookup_value for the MATCH function. Use a structured reference to the ID column (range A2:A36) as the lookup_array for the MATCH function. Specify an exact match (0) for the MATCH function. Use the Release Date column number (column 4) as the column_num for the INDEX function
