On the CallCenterRenort worksheet, add formulas that will summarize the issues for the department entered in cell B3 In cell B6, add an INDEX function that will use an INDIRECT function to retrieve the department issue list for the department listed in cell B3. Use an absolute reference to B3, and then use a relative cell reference to A6 as the tow.oum argument. Copy the formula down to cell B9 In cell C6, add a COUNTIFS function that will count the number of departments on the iCallData worksheet. Use the Dept named range as criteria_range1, and then use an absolute 10 reference to B3 as criteria1. Use the Reason named range as criteria range2, and then use cell A6 as criteria2. Copy the formula down to cell C9 In cell D6, add a COUNTIFS function that will count the number of calls coming from the department listed in B3. Use the Dept named range as criteria rangel, and then use an absolute reference to B3 as criterial. Use the Reason named range as criteria range2, and then use cell A6 as criteria2. Use the n Hold named range as criteria_ range3, and then use In cell E6, add an IF statement with a nested AND that will enter a status notice. If the number of calls on issue in cell C6 is greater than 3 and the number of calls on hold in cell D6 is greater than 2, then "Check Hold Issue" should display. Otherwise, nothing should display. Copy the formula down to cell E9 In cell B12, add an AVERAGEIF function that will find the average call length for the Dept named range and the criteria spedfied in cell A12. 12 13 In cell C12, add a COUNTIF function that will count the number of calls for the Dept named range and the criteria specified in cell A12. In cell D12, add a formula that sums two COUNTIFS formulas. The first COUNTIFS will count the number of calls assodated with the Dept named range and criteria specified in cell A12 that received a grade F, and the second COUNTIFS will do the same for grade D 14 On the CallCenterRenort worksheet, add formulas that will summarize the issues for the department entered in cell B3 In cell B6, add an INDEX function that will use an INDIRECT function to retrieve the department issue list for the department listed in cell B3. Use an absolute reference to B3, and then use a relative cell reference to A6 as the tow.oum argument. Copy the formula down to cell B9 In cell C6, add a COUNTIFS function that will count the number of departments on the iCallData worksheet. Use the Dept named range as criteria_range1, and then use an absolute 10 reference to B3 as criteria1. Use the Reason named range as criteria range2, and then use cell A6 as criteria2. Copy the formula down to cell C9 In cell D6, add a COUNTIFS function that will count the number of calls coming from the department listed in B3. Use the Dept named range as criteria rangel, and then use an absolute reference to B3 as criterial. Use the Reason named range as criteria range2, and then use cell A6 as criteria2. Use the n Hold named range as criteria_ range3, and then use In cell E6, add an IF statement with a nested AND that will enter a status notice. If the number of calls on issue in cell C6 is greater than 3 and the number of calls on hold in cell D6 is greater than 2, then "Check Hold Issue" should display. Otherwise, nothing should display. Copy the formula down to cell E9 In cell B12, add an AVERAGEIF function that will find the average call length for the Dept named range and the criteria spedfied in cell A12. 12 13 In cell C12, add a COUNTIF function that will count the number of calls for the Dept named range and the criteria specified in cell A12. In cell D12, add a formula that sums two COUNTIFS formulas. The first COUNTIFS will count the number of calls assodated with the Dept named range and criteria specified in cell A12 that received a grade F, and the second COUNTIFS will do the same for grade D 14
