Microsoft Office Tutorials and References

In Depth Information

**Going for the Count**

Table 9-1

Using Criteria with the COUNTIF Function

Example

Comment

=COUNTIF(D5:D329,”=2002”)

Returns the count of movies made in

2002.

=COUNTIF(D5:D329,2002)

Returns the count of movies made in

2002. Note that this is unique in that

the criteria do not need to be in double

quotes. This is because the criterion is

a simple equality.

=COUNTIF(D5:D329,”<2002”)

Returns the count of movies made

before 2002.

=COUNTIF(D5:D329,”>=2002”)

Returns the count of movies made in

or after 2002.

=COUNTIF(D5:D329,”<>2002”)

Returns the count of movies not made

in 2002.

The criteria can also be based on text. For example, COUNTIF can count all

occurrences of Detroit in a list of business trips. You can use wildcards with

COUNTIF. The asterisk (*) is used to represent any number of characters,

and the question mark (?) is used to represent a single character.

As an example, using an asterisk after Batman returns the number of Batman

movies found in Column B in Figure 9-23. A formula to do this looks like this:

=COUNTIF(B5:B329,”Batman*”). Notice the asterisk after Batman. This

lets the function count Batman and Robin, Batman Returns, and Batman

Forever, along with just Batman.

Your criterion can be entered in a cell rather than directly in the COUNTIF

function. Then just use the cell address in the function. For example, if you

enter “Batman*” in cell C1, then =COUNTIF(B5:B329,C1) would have

the same result as the previous example. Cell F11 in Figure 9-23 returns the

count of movies that have earned more than $200,000,000. The formula is

=COUNTIF(C5:C329,”>200000000”).

What if you need to determine the count of data items that match two

conditions? Can do! The formula in cell F15 returns the count of movies that were

made in 2004
and
earned more than $200,000,000. However, COUNTIF is not

useful for this type of multiple condition count. Instead, the SUMPRODUCT

function is used. The formula in cell F15 follows:

=SUMPRODUCT((C5:C329>200000000)*(D5:D329=2004))