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

Custom Search