Create a nested INDEX and MATCH function to display the number of responses from a city.

Click the Mailings sheet tab and select and name cells A$3$:D$28$ as Responses.

Click the Mailing Stats sheet tab.

Click cell B$21$ and type Carthage.

Click cell C$21,$ start an INDEX function, and select the first argument list option.

Choose or type the Responses range name for the Array argument.

Click the Row$\_$num box and nest a MATCH function. Select cell B$21$ for the Lookup$\_$value and cells A$3$:A$28$ on the Mailings sheet for the Lookup$\_$array. Click the Match$\_$type argument box and type $0.$

Click INDEX in the Formula bar. Click the Column$\_$num box and nest a second MATCH function to look up cell D$3$ on the Mailings sheet in the lookup array A$3$:D$3.$

Click the Match$\_$type box and type $0($Figure $6-106)$ and click OK$.$ The result displays as $343\mathrm{.}00.$

Format the results to show zero decimal places.

Type Smyrna in cell B$21.$

Use DSUM to summarize mailing data.

Select the Mailings sheet and note that number mailed is located in the third column and response data is in the fourth column.

Click the Criteria sheet tab. Select cell B$2$ and type lan$*$ to select data for the Landscape Design department.

Type law$*$ in cell B$5$ for the Lawn & Maintenance department.

Type pat$*$ in cell B$8$ for the Patio & Furniture department.

Click the Mailing Stats sheet tab and select cell B$7.$

Use DSUM with the range name Responses as the Database argument. Type $3$ for the Field argument $($# Mailed column$),$ and enter an absolute reference to cells B$1$:B$2$ on the Criteria sheet as the Criteria argument.

Copy the formula to cell C$7$ and edit the Field argument to use the fourth column $($# Responses$).$

Use DSUM in cells B$8$:C$9$ to calculate results for the two remaining departments.

Use SUM in cells B$10$:C$10.$

Format all values as Comma Style with no decimal places.

Create an IFS function to display a response rating. Note: If your version of Excel does not include the IFS function, build the following nested IF function $=$IF$($C$7/$B$7>=20\%,$$C$$15,$IF$($C$7/$B$7>=15\%,$$C$$16,$IF$($C$7/$B$7>=11\%,$$C$$17,$$C$$18)))$ to display the rating.

Click cell D$7.$ The response rate and ratings are shown in rows $14$:$18.$

Start an IFS function and select C$7$ for the Logical$\_$test$1$ argument. Type $/$ for division and select cell B$7.$ Type $>=20\%$ to complete the test.

Click the Value$\_$if$\_$true$1$ box, select C$15,$ and press F$4($FN$+$F$4)($Figure $6-107).$

Click the Logical$\_$test$2$ box, select C$7,$ type $/,$ select cell B$7,$ and type $>=15\%.$

Click the Value$\_$if$\_$true$2$ box, click cell C$16,$ and press F$4($FN$+$F$4).$

Complete the third and fourth logical tests and Value$\_$if$\_$true arguments $($Figure $6-108).$

Copy the formula in cell D$7$ to cells D$8$:D$10.$

Use SUMIFS to total insurance claims and dependents by city and department.

Click the Employee Insurance sheet tab and select cell E$25.$

Use SUMIFS with an absolute reference to cells F$4$:F$23$ as the Sum$\_$range argument.

The Criteria$\_$range$1$ argument is an absolute reference to cells E$4$:E$23.$ The Criteria$1$ argument is bre$*$ to select the city of Brentwood.

The Criteria$\_$range$2$ argument is an absolute reference to cells D$4$:D$23,$ the department column, with criteria of lan$*$ to select the Landscape Design department.

Click OK$.$ The result for cell E$25$ is $10.$

Build SUMIFS formulas for cells E$26$:E$28$ based on the criteria displayed in cells C$26$:C$28.$

Format borders to remove inconsistencies, if any, and adjust column widths to display data.

Use TEXTJOIN to display names. Note: If your version of Excel does not include the TEXTJOIN function, use CONCAT or CONCATENATE to build the function.

Click the Full Names sheet tab and select cell E$4.$

Start a TEXTJOIN function and press Spacebar for the Delimiter argument.

Click the Text$1$ box and select cell C$4.$

$.$

Copy the formula to display full names in column E$.$

Save and close the ClassicGardens$-06$ Excel workbook $($Figure $6-110).$

Upload and save the ClassicGardens$-06$ Excel workbook.

Submit project for grading.