Statistical IF Functions

This section demonstrates using statistical IF functions. Statistical IF functions allow you to evaluate the contents of 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, etc.

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 contains 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 by the range of cells that will be counted. The criteria argument is defined with the criteria used to decide if a cell in the range should be included in the function's output. 

For example, these steps explain how to 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).
   
   
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 shows the completed Function Arguments dialog box for the COUNTIF function. Notice the absolute references placed on each cell location in the range used to define the Range argument. The Criteria argument is defined with 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 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

 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.