Using COUNTIF In Excel

Why would you use the COUNTIF function? This is a question that you may wonder at times. First of all, you could use it to figure out how many cells in a range match the specified criteria. You could also use it to determine whether or not a value in one cell is found in a range of other cells. The following is an example table which will be used to accomplish both of these tasks:

As you can see, cells D2 to D7 should indicate whether or not the patient is also a relative. To do this, do the following:

1. Enter =COUNTIF($A$2:$A$7,$C2)>0 into cell D2.
2. Select cell D2 and use the CTRL + C key combination to copy the cell.
3. Select cells D3 to D7 and use the CTRL + V key combination to paste the relative formula into these cells.

Now the table should look like the following:

This formula starts off by counting the number of times the value in the same row and in column C was found in the range of A2 to A7. Next, the cell determines whether or not the count of these matches is greater than 0. If the count is greater than zero, the cell evaluates to TRUE. If the count is not greater than zero, the cell evaluates to FALSE.

Now it is time to figure out the count those under 18, compared to those who are at least 18:

1. Enter =COUNTIF($B$2:$B$7,"<18") into cell B9.
2. Enter =COUNTIF($B$2:$B$7,">=18") into cell B10.

Now the table should look like the following:

The formula in cell B9 simply counts how many cells in the range B2 to B7 are less than 18. The formula in cell B10 counts how many cells in the range B2 to B7 are greater than or equal to 18.