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))
Search JabSto ::

Custom Search