COUNTIF and COUNTIFS use conditional criteria for counting cells and is specifically used in places where there is a need to maintain large and diverse databases based solely on numerical data. For example, consider a school database wherein the number of students whose age is 16 or above needs to be selected. This can be calculated simply using the COUNTIF function and the greater-than condition.
Let us discuss COUNTIF and COUNTIFS function to get acquainted with the conditional counting of cells.
The COUNTIF function allows for the counting of cells based upon a criterion. For example, suppose we want to count cells containing values greater than 1, in a range of cells, say from A1 to Q19. There are two methods you can use to apply the COUNTIF function.
COUNTIF Method 1: Using a dialog box
- Click on the cell in Excel where you want the result to appear.
- Click on the Formulas Tab on Menu Bar, click on More Functions option under Function Library section, and further click on Statistical.
- Click on COUNTIF function.
- Under Function Arguments dialog box, select the range of cells on which COUNTIF needs to be applied along with the criteria.
- Click on OK.
COUNTIF Method 2: Typing in the syntax directly
In its simplest form, the syntax of COUNTIF can be generalized to 2 arguments:
=COUNTIF (Where do you want to look?, What do you want to look for?)
For the previous example, the syntax of the COUNTIF function would look like:
=COUNTIF (A1: Q19, >1)
The result of the following function would be displayed in the selected cell.
Thus, to directly type in the syntax, the following sequence of steps can also be followed:
- Click on the cell where you want the result to appear.
- Click on the Formula Bar, available under Menu Bar, and type the function following the syntax:
=COUNTIF (A1: Q19, >1)
- Press Enter key on keyboard.
The result for the following function would be displayed in the selected cell.
COUNTIFS function works like COUNTIF but rather than count cells based on one criterion, it counts cells based upon multiple criteria.
In its simplest form, the syntax of COUNTIF can be generalized to:
COUNTIFS(criteria_ range1, criteria1, [criteria_ range2, criteria2], …)
COUNTIFS function can thus contain multiple arguments, based upon the ranges on which the function is applied and their corresponding criteria.
Here are two examples:
In order to count how many times Argentina and India appear in a specific range of cells, you can use the following formula:
=COUNTIFS (B2: B5, “Argentina”, B12: B15, “India”)
In order to count how many times the value of B9 and the value of B19 appear in a specific range of cells, you can use:
=COUNTIFS (B2: B5, B9, B12: B15, B19)
COUNTIFS can also be used by following the same two methods above. Just remember to include the ‘s’ in COUNTIFS and write the appropriate syntax if you are using method 2.