What function counts the number of cells with numerical data in the argument?

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:

1. Click cell B4 on the Portfolio Summary worksheet.
2. Click the Formulas tab of the Ribbon.
3. Click the More Functions button in the Function Library group of commands.
4. Place the mouse pointer over the Statistical option from the drop-down list.
5. 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).

6. Click the COUNTIF function. This will open the Function Arguments dialog box.

   

   Figure 1 Selecting the COUNTIF Function from the Function Library

7. 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).
8. Click the Investment Detail worksheet tab.
9. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
10. 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.
11. 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.
12. 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

13. Copy the function in cell B4 and paste it into the range B5:B7 using the Paste Formulas command.
14. 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

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

Which Excel function that counts of the cells that contain numerical data in the selected range of cells?

Which function is used to count the number of cells having any data a count () b Counta () C counts () d Countb ()?

What is the function name to count the cell or number of cell?

What is the function that adds all the values of the cells in the argument?