## Statistical IF Functions

Read these examples of how to use these different functions in business. The learning objectives review each type of function's use, and the tables describe the syntax for each type of command in detail.

### The SUMIFS Function

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.

#### Integrity Check

Error Message for Statistical IF Functions

If you receive an error message when attempting to enter a statistical IF function into a cell location, check to make sure any criteria using a comparison operator is enclosed in quotation marks. For example, if you are assessing a range of cells to see if the values are greater than or equal to zero, you must define the Criteria argument as follows: “>=0”. If you do not use the quotation marks, Excel will not accept the function in the cell location and will display an error message.

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

#### Skill Refresher: SUMIFS Function

1. Type an equal sign: =
2. Type the function name SUMIFS followed by an open parenthesis: (
3. Define the Sum_range argument with a range of cells that contain values to be summed.
4. Type a comma.
5. Define the Criteria_range1 argument with a range of cells that will be evaluated based on the criteria used to define the Criteria1 argument.
6. Type a comma.
7. Define the Criteria1 argument with a cell location, number, text, or logical test. Text and logical tests must be enclosed in quotation marks.
8. Repeat steps 4, 5, 6, and 7 to define as many pairs of criteria arguments as needed.
9. Type a closing parenthesis: )
10. Press the ENTER key on your keyboard.

#### Key Takeaways

• The COUNTIF, SUMIF, and AVERAGEIF functions can select specific cell locations from a range to compute an output using one criteria argument.
• The COUNTIFS, SUMIFS, and AVERAGEIFS functions can select specific cell locations from a range to compute an output using multiple sets of criteria arguments.
• When using the AVERAGEIF and SUMIF functions, the row numbers used in the ranges to define the Range argument and the Average_range or Sum_range arguments must be identical.
• The benefit of using statistical functions when creating a summary worksheet is that if rows of data are added to the detail worksheet, the new data will automatically be included in the function output.
• When using statistical IF functions, the logical test used to define a criteria argument must be enclosed in quotation marks.