Microsoft Office Tutorials and References

In Depth Information

**Counting cells by using the COUNTIF function**

Counting cells by using the COUNTIF function

The
COUNTIF
function, which is useful for single-criterion counting formulas, takes two

arguments:

■
range
: The range that contains the values that determine whether to include a

particular cell in the count

■
criteria
: The logical criteria that determine whether to include a particular cell

in the count

Table 17.2 lists several examples of formulas that use the
COUNTIF
function. These

formulas all work with a range named Data. As you can see, the criteria argument proves quite

ﬂ exible. You can use constants, expressions, functions, cell references, and even wildcard

characters (
*
and
?
).

17

Note that the
COUNTIF
functions works only with a contiguous range argument. If
Data
is

deﬁ ned as a noncontiguous range, the formula returns a
#VALUE!
error.

TABLE 17.2
Examples of Formulas Using the COUNTIF Function

=COUNTIF(Data,12)

Returns the number of cells containing the value
12

=COUNTIF(Data,"<0")

Returns the number of cells containing a negative value

=COUNTIF(Data,"<>0")

Returns the number of cells not equal to 0

=COUNTIF(Data,">5")

Returns the number of cells greater than 5

=COUNTIF(Data,A1)

Returns the number of cells equal to the contents of cell A1

=COUNTIF(Data,">"&A1)

Returns the number of cells greater than the value in cell A1

=COUNTIF(Data,"*")

Returns the number of cells containing text

=COUNTIF(Data,"???")

Returns the number of text cells containing exactly three

characters

=COUNTIF(Data,"budget")

Returns the number of cells containing the single word

budget
(not case sensitive)

=COUNTIF(Data,"*budget*")

Returns the number of cells containing the text
budget

anywhere within the text

=COUNTIF(Data,"A*")

Returns the number of cells containing text that begins with

the letter
A (not case sensitive)

=COUNTIF(Data,TODAY())

Returns the number of cells containing the current date

=COUNTIF(Data,">"&AVERAGE

(Data))

Returns the number of cells with a value greater than the

average of the values

=COUNTIF(Data,">"&AVERAGE

(Data)+STDEV(Data)*3)

Returns the number of values exceeding three standard

deviations above the mean

Continues