# Statistical IF Functions

## The COUNTIF Function

This section will demonstrate the use of statistical IF functions. **Statistical IF functions** provide you with the ability to evaluate the contents in a cell location before including them in a mathematical calculation. This allows you
to selectively include targeted cell locations when executing statistical calculations such as sum, average, count, and so on.

The COUNTIF function differs from the regular COUNT function in two ways. First, the regular COUNT function counts only the number of cells in a range that contain numeric data. The COUNTIF function counts the number of cells in a range that contain numeric or text data. Second, the COUNTIF function allows you to selectively count the cells in a range based on specific criteria.

The COUNTIF function contains two arguments: **range** and **criteria**. The **range** argument is defined with the range of cells that will be counted. The **criteria** argument is defined with the
criteria that will be used to decide if a cell in the range should be included in the output of the function. The following steps explain how we can use the COUNTIF function to calculate the number of investments by investment type on the **Portfolio Summary **worksheet:

- Click cell B4 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 COUNTIF function (see
**Figure 1 Selecting the COUNTIF Function from the Function Library**). -
Click the COUNTIF function. This will open the Function Arguments dialog box.

Figure 1 Selecting the COUNTIF Function from the Function Library

- Click the Collapse Dialog button next to the
**Range**argument on the Function Arguments dialog box (see**Figure 2 Completed Function Arguments Dialog Box for the COUNTIF 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
**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. Alternatively, place the insertion point after the 4 in cell reference A4 and press the F4 key on your keyboard. Then place the insertion point after the 8 in cell reference A18 and press the F4 key. This will add the $ to the appropriate positions automatically. - Press the TAB key on your keyboard to advance to the next argument, which is the
**Criteria**argument. Then type the cell location**A4**. The criteria for the function will be the investment type entered into cell A4 on the**Portfolio Summary**worksheet. - Click the OK button at the bottom of the Function Arguments dialog box.
**Figure 2 Completed Function Arguments Dialog Box for the COUNTIF Function**shows the completed Function Arguments dialog box for the COUNTIF function. Notice the absolute references that were placed on each cell location in the range that was used to define the**Range**argument. The**Criteria**argument is defined with the cell A4, which means the function will only count cell locations in the range A4:A18 where the contents in the cell match the contents in cell A4.

Figure 2 Completed Function Arguments Dialog Box for the COUNTIF Function

- Copy the function in cell B4 and paste it into the range B5:B7 using the Paste Formulas command.
- Enter a SUM function in cell B8 that sums the values in the range B4:B7.

**Figure 3 COUNTIF Function Output in the Portfolio Summary Worksheet** shows the results of the COUNTIF function after it is pasted into the range B5:B7. Because of relative referencing, the cell location used in the

**criteria**argument is changed after the function is pasted into the range B5:B7. For example, in cell B6, the function is counting the cell locations in the range A4:A18 where the contents match the contents of cell A6. This allows you to use the function to count the number of investments per investment type. As shown in the figure, the range B4:B7 now shows the number of investments in this portfolio by investment type.

Figure 3 COUNTIF Function Output in the Portfolio Summary Worksheet

#### Skill Refresher: COUNTIF Function

- Type an equal sign:
**=** - Type the function name
**COUNTIF**followed by an open parenthesis:**(** - Define the
**range**argument with a range of cells that will be counted. - Type a comma.
- Define the
**criteria**argument with a cell location, number, text, or logical test. Text and logical tests must be enclosed in quotation marks. - Type a closing parenthesis:
**)** - Press the ENTER key on your keyboard.

This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.