## 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 COUNTIFS Function

Up to this point, the statistical IF functions that were demonstrated provided the ability to define one criteria or logical test used to select cells from a targeted range. The next set of statistical functions that will be demonstrated provides the ability to define multiple sets of criteria for selecting cells from a targeted range. We will begin with the COUNTIFS function.

It is easy to distinguish the difference between a statistical IF function that allows one criteria argument to be defined and one that allows multiple criteria arguments. If the IF at the end of the function name is plural, you can define multiple sets
of criteria arguments. Therefore, the COUNTIF*S* function provides the option of defining multiple sets of criteria for selecting cells from a targeted range that will be used in the function output. The arguments for the COUNTIFS function
are established in pairs. For example, the first arguments for the function are **Criteria_range1** and **Criteria1**. The function will use the **Criteria1** argument to select cells in the **Criteria_range1** argument. A second pair of arguments, **Criteria_range2** and **Criteria2**, can be defined to select a subset of cell locations that were selected in the **Criteria_range1** and **Criteria1** arguments.
This process can be repeated for several pairs of criteria arguments. We will continue to work on the **Portfolio Summary** worksheet by adding the COUNTIFS function to count the number of poor performing investments by investment type.
The following steps explain how to add this function to the worksheet:

- Click cell B11 on the
**Portfolio Summary**worksheet. - Click the Formulas tab of the Ribbon.
- Click the More Functions button in the Function Library group of commands.
- Place the mouse pointer over the Statistical option from the drop-down list.
- Click the scroll down arrow on the second drop-down list to find the COUNTIFS function (see
**Figure 1 Selecting the COUNTIF Function from the Function Library**). - Click the COUNTIFS function. This will open the Function Arguments dialog box.
- Click the Collapse Dialog button next to the
**Criteria_range1**argument on the Function Arguments dialog box (see**Figure 9 Defined Arguments for the COUNTIFS Function**). - Click the
**Investment Detail**worksheet tab. - Highlight the range A4:A18 on the
**Investment Detail**worksheet and press the ENTER key on your keyboard. - 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. - 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 when you define this argument, the**Criteria_range2**argument will appear on the Function Arguments dialog box. - Click the Collapse Dialog button next to the
**Criteria_range2**argument on the Function Arguments dialog box (see**Figure 9 Defined Arguments for the COUNTIFS Function**). - Click the
**Investment Detail**worksheet tab. - Highlight the range W4:W18 on the
**Investment Detail**worksheet and press the ENTER key on your keyboard. - 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. - Press the TAB key on your keyboard to advance to the
**Criteria2**argument. - 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%. - Click the OK button at the bottom of the Function Arguments dialog box.
- Copy the function in cell B11 and paste it into the range B12:B14 using the Paste Formulas command.
- Enter a SUM function in cell B15 on the
**Portfolio Summary**worksheet that sums the values in the range B11:B14.

Why?

Use Statistical IF Functions for a Summary Worksheet

When creating a summary worksheet that summarizes detailed data from other worksheets, such as the **Portfolio Summary** worksheet in **Figure 8 Completed Annual Growth Column in the Portfolio Summary Worksheet**, it is best
to use statistical IF functions. If data is added to the detailed worksheet that is being summarized, the statistical IF functions will automatically include the new data in the summary worksheet. For example, suppose a row is added below Row 7 on
the **Investment Detail** worksheet (see **Figure 3.24 "Conditional Format Applied to the Range S4:T18"**) and another bond investment is added. The statistical IF functions will automatically pick up the new investment and
include it in the **Portfolio Summary** worksheet. If regular statistical functions or formulas are used, the summary worksheet can easily become inaccurate if new data is added to the detailed worksheet or if the sort order is changed.

**Figure 9 Defined Arguments for the COUNTIFS Function** shows the completed arguments for the COUNTIFS function in the Function Arguments dialog box. Notice the scroll bar that appears on the right side of the Function Arguments dialog
box. This allows you to scroll through and define multiple pairs of criteria arguments for the function.

Figure 9 Defined Arguments for the COUNTIFS Function

**Figure 10 Outputs for the COUNTIFS Functions** shows the output of the COUNTIFS function on the

**Portfolio Summary**worksheet. The criteria established for the COUNTIFS function shows that there are a total of seven investments that are underperforming in the portfolio.

Figure 10 Outputs for the COUNTIFS Functions

#### Skill Refresher: COUNTIFS Function

- Type an equal sign:
**=** - Type the function name
**COUNTIFS**followed by an open parenthesis:**(** - Define the
**Criteria_range1**argument with a range of cells that will be counted. - Type a comma.
- Define the
**Criteria1**argument with a cell location, number, text, or logical test. Text and logical tests must be enclosed in quotation marks. This argument will be used to select cells from the**Criteria_range1**argument. - Type a comma and then repeat steps 3, 4, and 5 to define as many pairs of arguments as needed.
- Type a closing parenthesis:
**)** - Press the ENTER key on your keyboard.