PRDV006: Spreadsheets II: Formatting and Functions (2021.A.01)

The SUMIFS function is similar to the AVERAGEIFS function in that multiple criteria arguments can be defined to select cells from a targeted range (see Table 2 Arguments for the AVERAGEIFS and SUMIFS Functions). The function will use the selected cells from this targeted range to calculate a total or sum. The SUMIFS function will be used to complete the Total Purchase Cost and Current Value columns in the Poor Performing Investments section of the Portfolio Summary worksheet. The following steps explain how to construct this function for the Total Purchase Cost column:

1. Click cell D11 on the Portfolio Summary worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Math & Trig button in the Function Library group of commands (see Figure 1 Selecting the COUNTIF Function from the Function Library).
4. Select the SUMIFS function from the drop-down list. Use the scroll bar to scroll down to find the SUMIFS function. This will open the Function Arguments dialog box.
5. Click the Collapse Dialog button next to the Sum_range argument on the Function Arguments dialog box (see Figure 13 Completed Arguments for the SUMIFS Function).
6. Click the Investment Detail worksheet tab.
7. Highlight the range G4:G18 on the Investment Detail worksheet and press the ENTER key on your keyboard. Selected cells from this range will be summed based on the defined criteria in ensuing arguments.
8. Click in the Sum_range argument input box and place an absolute reference on the range G4:G18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
9. Click the Collapse Dialog button next to the Criteria_range1 argument on the Function Arguments dialog box (see Figure 13 Completed Arguments for the SUMIFS Function).
10. Click the Investment Detail worksheet tab.
11. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard. Once the Criteria_range1 argument is defined, the Criteria1 argument will appear in the Function Arguments dialog box.
12. Click in the Criteria_range1 argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
13. Press the TAB key on your keyboard to advance to the Criteria1 argument and type the cell location A11. The criteria for the function will be the investment type that is entered into cell A11 on the Portfolio Summary worksheet. You will notice that as soon as you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.
14. Click the Collapse Dialog button next to the Criteria_range2 argument on the Function Arguments dialog box (see Figure 13 Completed Arguments for the SUMIFS Function).
15. Click the Investment Detail worksheet tab.
16. Highlight the range W4:W18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
17. Click in the Criteria_range2 argument input box and place an absolute reference on the range W4:W18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
18. Press the TAB key on your keyboard to advance to the Criteria2 argument.
19. Type an open quotation mark followed by the logical test <−1% and then type a closing quotation mark (“<−1%”). The second criterion for this function is a logical test that will identify cell locations where the value is less than −1%. For the purposes of this exercise, a poor performing investment is one that is below the target growth rate by more than 1%.
20. Click the OK button at the bottom of the Function Arguments dialog box.
21. Copy the function in cell D11 and paste it into the range D12:D14 using the Paste Formulas command.
22. Enter a regular SUM function in cell D15 on the Portfolio Summary worksheet that sums the values in the range D11:D14.

Figure 13 Completed Arguments for the SUMIFS Function shows the defined arguments for the SUMIFS function. Values that meet all criteria defined in the function will be selected from the range G4:G18. Notice that the results for each argument of the function are shown along the right side of the Collapse Dialog buttons.

Figure 13 Completed Arguments for the SUMIFS Function

Figure 14 SUMIFS Function Output for the Total Purchase Cost Column shows the results of the SUMIFS function used to complete the Total Purchase Cost column. In total, over $45,000 was invested in funds and stocks that are not meeting the performance goals of the portfolio.

Figure 14 SUMIFS Function Output for the Total Purchase Cost Column

In addition to the Total Purchase Cost column, the SUMIFS function will also be used to complete the Current Value column for the Poor Performing Investments section on the Portfolio Summary worksheet. The following steps explain how to add the function to the worksheet to complete this column:

1. Click cell E11 on the Portfolio Summary worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Math & Trig button in the Function Library group of commands (see Figure 1 Selecting the COUNTIF Function from the Function Library).
4. Select the SUMIFS function from the drop-down list. Use the scroll bar to scroll down to find the SUMIFS function. This will open the Function Arguments dialog box.
5. Click the Collapse Dialog button next to the Sum_range argument on the Function Arguments dialog box (see Figure 15 Completed Function Arguments Dialog Box for the Second SUMIFS Function).
6. Click the Investment Detail worksheet tab.
7. Highlight the range K4:K18 on the Investment Detail worksheet and press the ENTER key on your keyboard. Selected cells from this range will be summed based on the defined criteria in ensuing arguments.
8. Click in the Sum_range argument input box and place an absolute reference on the range K4:K18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
9. Click the Collapse Dialog button next to the Criteria_range1 argument on the Function Arguments dialog box (see Figure 15 Completed Function Arguments Dialog Box for the Second SUMIFS Function).
10. Click the Investment Detail worksheet tab.
11. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard. Once the Criteria_range1 argument is defined, the Criteria1 argument will appear in the Function Arguments dialog box.
12. Click in the Criteria_range1 argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
13. Press the TAB key on your keyboard to advance to the Criteria1 argument and type the cell location A11. The criteria for the function will be the investment type entered into cell A11 on the Portfolio Summary worksheet.
14. Click the Collapse Dialog button next to the Criteria_range2 argument on the Function Arguments dialog box (see Figure 15 Completed Function Arguments Dialog Box for the Second SUMIFS Function).
15. Click the Investment Detail worksheet tab.
16. Highlight the range W4:W18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
17. Click in the Criteria_range2 argument input box and place an absolute reference on the range W4:W18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
18. Press the TAB key on your keyboard to advance to the Criteria2 argument.
19. Type an open quotation mark followed by the logical test <−1% and then type a closing quotation mark (“<−1%”). The second criterion for this function is a logical test that will identify cell locations where the value is less than −1%. For the purposes of this exercise, a poor performing investment is one that is below the target growth rate by more than 1%.
20. Click the OK button at the bottom of the Function Arguments dialog box.
21. Copy the function in cell E11 and paste it into the range E12:E14 using the Paste Formulas command.
22. Enter a regular SUM function in cell E15 on the Portfolio Summary worksheet that sums the values in the range E11:E14 (see Figure 16 SUMIFS Function Output for the Current Value Column).
    

Figure 15 Completed Function Arguments Dialog Box for the Second SUMIFS Function

Figure 16 SUMIFS Function Output for the Current Value Column

Now that the Total Purchase Cost and Current Value columns are completed for the Poor Performing Investments section on the Portfolio Summary worksheet, we can add a formula to show the annual growth rate by investment type. Since this is the same formula that was used in the Total Summary section of the Portfolio Summary worksheet, we can just copy and paste it. The following steps explain how to do this:

1. Copy the formula in cell F4 on the Portfolio Summary worksheet.
2. Highlight the range F11:F15 on the Portfolio Summary worksheet.
3. Paste the formula using the Paste Formulas command.

Figure 17 Completed Poor Performing Investments Section of the Portfolio Summary Worksheet shows the completed Poor Performing Investments section on the Portfolio Summary worksheet. Notice that even though an investment is considered “poor performing” it does not mean that money is lost on the investment. As shown in the figure, only one investment in the International Stock Fund category is losing money, with an annual growth rate of −5.4%. However, the total annual growth rate for all investments in the Poor Performing section is 2.2% (see cell F15). This is less than half the growth rate for the overall portfolio, which is 6.2% (see cell F8). A final observation is that all the investments in the Domestic Stock Fund category are “poor performing.” The same number of investments in the Total Summary section appears in the Poor Performing Investments section. This completes the Portfolio Summary worksheet with the exception of one column. We will complete this column in the next section with a lookup function.

Figure 17 Completed Poor Performing Investments Section of the Portfolio Summary Worksheet