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:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West
3 Beth Coope 24 Elizabeth Coope
4 Dan Yoder 17 Vladimir Yoder
5 Susie Queue 19 Beth Coope
6 Matthew Franklin 18 Jeffrey Franklin
7 Vladimir Yoder 25 Victoria Yoder
8
9 Under 18:
10 At least 18:

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:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West FALSE
3 Beth Coope 24 Elizabeth Coope FALSE
4 Dan Yoder 17 Vladimir Yoder TRUE
5 Susie Queue 19 Beth Coope TRUE
6 Matthew Franklin 18 Jeffrey Franklin FALSE
7 Vladimir Yoder 25 Victoria Yoder FALSE
8
9 Under 18:
10 At least 18:

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:

A B C D
1 Patient Name Age Relative Name Relative Is Patient
2 Chris West 22 Jim West FALSE
3 Beth Coope 24 Elizabeth Coope FALSE
4 Dan Yoder 17 Vladimir Yoder TRUE
5 Susie Queue 19 Beth Coope TRUE
6 Matthew Franklin 18 Jeffrey Franklin FALSE
7 Vladimir Yoder 25 Victoria Yoder FALSE
8
9 Under 18: 1
10 At least 18: 5

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.

Categories: BlogMicrosoft Office