Related Tutorial: Step by Step Tutorial with Screen Shots - Excel COUNTIFS Function
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.
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.
Example: Using Excel's COUNTIFS Function
In this example we will search cells D1 to D6 for values equal to 135 and cells F1 to F6 for values that are equal to 165.
Excel will only count those instances when the values 165 and 135 occur in the same row within their respective ranges.
For help with this example, see the image above.
- Enter the following data into cells D1 to D6: 123, 135, 147, 135, 98, 135.
- Enter the following data into cells E1 to E6: 165, 165, 178, 143, 130, 165.
- Click on cell F1 - the location where the results will be displayed.
- Click on the Formulas tab.
- Choose More Functions > Statistical from the ribbon to open the function drop down list.
- Click on COUNTIFS in the list to bring up the function's dialog box.
- In the dialog box, click on the button at the end of the Criteria_range1 line to return to your spreadsheet.
- Drag select cells D1 to D6 on the spreadsheet.
- Click on the button at the end of the Criteria_range1 line to return to the dialog box.
- On the Criteria1 line in the dialog box, type the number "135".
- Click on the button at the end of the Criteria_range2 line to return to your spreadsheet.
- Drag select cells E1 to E6.
- Click on the button at the end of the Criteria_range2 line to return to the dialog box.
- On the Criteria2 line in the dialog box, type the number "165".
- Click OK.
- 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 (row 2 and row 6) when the numbers 135 and 165 appear in the same row within their respective ranges.
- When you click on cell F1 the complete function
= COUNTIFS ( D1 : D6 , 135 , E1 : E6 , 165 ) appears in the formula bar above the worksheet .