Advertisement

Using the Excel COUNTIF function.

The COUNTIF function finds all of the values in a range that meet the criteria you specify and counts them.

Syntax:

COUNTIF( rangecriteria )

Range - The range of values to test the criteria against.

Criteria - The condition that must be met for a value to be included in the count.

The criteria can be a date, a time, an integer, a decimal, a logical (TRUE or FALSE), text, or a logical expression. A logical expression is an expression that includes a comparison operator, for example ">10".  Text and logical expressions must be enclosed in quotes. If your logical expression references a cell, place an ampersand(&) before the cell address ("<"&A1).  Text criteria is not case sensitive, so "DOG" and "dog" are equivalent.

You can use wildcards with text criteria. The asterisk  (*) matches any sequence of characters. For example, ABC*123 matches, ABC---123, ABC-/@4-123 and ABC+123. The question mark (?) matches any single character. For example, ABC?123 matches, ABC-123, ABC/123 and ABCD123. It will not match ABC---123 or ABC-/@4-123. If you want to match the asterisk (*) or the question mark, place a tilde (~) before the character.

Example 1: You have a list of people. Their sex is listed in cells B2 through B11. You want to find the number of people who are female.

=COUNTIF(B2:B11,"FEMALE")

Excel: COUNTIF example 1

Example 2: You have a list of people. Their age is listed in cells C2 through C11. You want to find the number of people who are over 40.

=COUNTIF(C2:C11,">40")

Excel: COUNTIF example 2

Example 3: You have a list of people. Their age is listed in cells C2 through C11. You want to find the number of people whose age is greater than the criteria in cell F1.

=COUNTIF(C2:C11,">"&F1)

Excel: COUNTIF example 3

In the following worksheet, all of the cells with orange text contain a calculation. Double click in the field to display the formula. Feel free to use the worksheet to experiment in any way you like.


Leave a Comment



HTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>