Excel 2007 COUNTIFS Function

The COUNTIFS function, similar to Excel's COUNTIF function, counts up the number of times data in two or more ranges of cells meets multiple criterion.

The ranges must be of identical size, and the function only counts instances where the criterion for each range is met simultaneously - such as in the same row.

The syntax for the COUNTIFS function is:

=COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...)

Criteria_range - the group of cells the function is to search.

Criteria - determines whether the cell is to be counted or not.

Note: Up to 127 range/criteria pairs can be entered into the function.

Example Using Excel's COUNTIFS Function:

Note: For help with this example, see the image to the right.

1. Enter the following data into cells D1 to D6: 123,135,147,135,98,135.
   
   
2. Enter the following data into cells E1 to E6: 165,165,178,143,130,165.
   
   
3. Click on cell F1 - the location where the results will be displayed.
   
   
4. Click on the Formulas tab.
   
   
5. Choose More Functions > Statistical from the ribbon to open the function drop down list.
   
   
6. Click on COUNTIFS in the list to bring up the function's dialog box.
   
   
7. In the dialog box, click on the button at the end of the Criteria_range1 line to return to your spreadsheet.
   
   
8. Drag select cells D1 to D6 on the spreadsheet.
   
   
9. Click on the button at the end of the Criteria_range1 line to return to the dialog box.
   
   
10. On the Criteria1 line in the dialog box, type the number "135".
    
    
11. Click on the button at the end of the Criteria_range2 line to return to your spreadsheet.
    
    
12. Drag select cells E1 to E6.
    
    
13. Click on the button at the end of the Criteria_range2 line to return to the dialog box.
    
    
14. On the Criteria2 line in the dialog box, type the number "165".
    
    
15. Click OK.
    
    
16. The answer 2 should appear in cell F1. Even though the numbers 135 and 165 appear three times in their respective ranges, there are only two instances when the numbers 135 and 165 appear in the same row within these ranges.
    
    
17. The complete function = COUNTIFS ( D1 : D6 , 135 , E1 : E6 , 165 ) appears in the formula bar above the worksheet when you click on cell F1.
    
    

• Excel 2007 COUNT Function
• Excel 2007 COUNTA Function
• Excel 2007 COUNTBLANK