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

The SUMIF function performs the same mathematical calculation as the regular SUM function. However, similar to the AVERAGEIF function, this function allows you to select specific cells from a range that will be used in the output. The arguments for the SUMIF function are identical to the AVERAGEIF function (see Table 1 Arguments for the AVERAGEIF and SUMIF Functions. We will use the SUMIF function in two columns on the Portfolio Summary worksheet. The first column will show the total investment cost for each investment type. The second column will show the total current value for each investment type. This will allow us to calculate the total annual growth rate for each investment type. The following steps explain how we will use this function to complete the first column:

1. Click cell D4 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 SUMIF function from the drop-down list. Use the scroll bar to scroll down to find the SUMIF function.
5. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 6 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet).
6. Click the Investment Detail worksheet tab.
7. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
8. Click in the Range 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.
9. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.
10. Click the Collapse Dialog button next to the Sum_range argument on the Function Arguments dialog box (see Figure 6 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet).
    
    




Figure 6 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet



11. Click the Investment Detail worksheet tab.
12. Highlight the range G4:G18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
13. 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.
14. Click the OK button at the bottom of the Function Arguments dialog box.
15. Copy the function in cell D4 and paste it into the range D5:D7 using the Paste Formulas option.
16. Enter a regular SUM function into cell D8 on the Portfolio Summary worksheet to calculate the sum of the values in the range D4:D7.

Figure 6 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet shows how the SUMIF arguments were defined for the Total Purchase Cost column on the Portfolio Summary worksheet. Notice that the row numbers are identical in the range used to define the Range argument and the Sum_range argument.

The following steps explain how to add the SUMIF function to the second column on the Portfolio Summary worksheet:

1. Click cell E4 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 SUMIF function from the drop-down list. Use the scroll bar to scroll down to find the SUMIF function.
5. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 6 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet).
6. Click the Investment Detail worksheet tab.
7. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
8. Click in the Range 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.
9. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4.
10. Click the Collapse Dialog button next to the Sum_range argument on the Function Arguments dialog box (see Figure 6 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet).
11. Click the Investment Detail worksheet tab.
12. Highlight the range K4:K18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
13. 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.
14. Click the OK button at the bottom of the Function Arguments dialog box.
15. Copy the function in cell E4 and paste it into the range E5:E7 using the Paste Formulas option.
16. Enter a regular SUM function into cell E8 on the Portfolio Summary worksheet to calculate the sum of the values in the range E4:E7.

Figure 7 SUMIF Function Outputs in the Portfolio Summary Worksheet shows the results of the SUMIF function in the Total Purchase Cost and Current Value columns in the Portfolio Summary worksheet.

Figure 7 SUMIF Function Outputs in the Portfolio Summary Worksheet

A formula can now be added to show the annual growth for each investment category. The following steps explain how to add this formula to the Portfolio Summary worksheet:

1. Click cell F4 on the Portfolio Summary worksheet.
2. Type an equal sign (=) followed by two open parenthesis: ((
3. Click cell E4 and type a minus sign: −
4. Click cell D4 and type a closing parenthesis: )
5. Type a slash (/) for division and click cell D4.
6. Type a closing parenthesis ()). This completes the first part of the formula, which is calculating the growth rate between the Total Purchase Cost (cell D4) and the Current Value (cell E4).
7. Type a slash (/) for division followed by an open parenthesis: (
8. Click cell C4, which is the Average Months Owned.
9. Type a slash (/) for division and the number 12. This part of the formula converts the number of months owned to years by dividing it by 12. This result is being divided into the growth rate, which will then show the average growth per year.
10. Type a closing parenthesis ( ) ) and press the ENTER key on your keyboard.
11. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste Formulas command.
    
    

Figure 8 Completed Annual Growth Column in the Portfolio Summary Worksheet shows the results of the statistical IF functions that were added to the Total Summary section of the Portfolio Summary worksheet. The statistical IF functions used on this worksheet allowed us to group the details in the Investment Detail worksheet by investment type. Once this was accomplished, we added a formula to show the annual growth rate by investment type.

Figure 8 Completed Annual Growth Column in the Portfolio Summary Worksheet