Microsoft Office Tutorials and References
In Depth Information
Using the COUNTIF Function
Using the COUNTIF Function
The Excel COUNT and COUNTA functions are useful for basic counting, but sometimes you need
more flexibility. This tip contains many examples of the powerful Excel COUNTIF function, useful
for counting cells based on various types of criteria.
These formulas all work with a range named Data, so you need to adjust the formulas to refer to
your own range. As you can see in Table 115-1, the criteria argument proves quite flexible. You
can use constants, expressions, functions, cell references, and even wildcard characters (* and ?).
Table 115-1: COUNTIF Formulas
Formula
Number of Cells or Values It Returns
=COUNTIF(Data,12)
Cells containing the value 12
=COUNTIF(Data,”<0”)
Cells containing a negative value
=COUNTIF(Data,”<>0”)
Cells not equal to 0
=COUNTIF(Data,”>5”)
Cells greater than 5
=COUNTIF(Data,A1)
Cells equal to the contents of cell A1
=COUNTIF(Data,”>”&A1)
Cells greater than the value in cell A1
=COUNTIF(Data,”*”)
Cells containing text
=COUNTIF(Data,”???”)
Text cells containing exactly three characters
=COUNTIF(Data,”budget”)
Cells containing the single word budget and nothing
else (not case sensitive)
=COUNTIF(Data,”*budget*”)
Cells containing the text budget anywhere within the
text (not case sensitive)
=COUNTIF(Data,”A*”)
Cells containing text that begins with the letter A (not
case sensitive)
=COUNTIF(Data,TODAY())
Cells containing the current date
=COUNTIF(Data,”>”&AVERAGE(Data))
Cells with a value greater than the average
=COUNTIF(Data,”>”&AVERAGE(Data)+
STDEV(Data)*3)
Values exceeding three standard deviations above the
mean
=COUNTIF(Data,3)+COUNTIF(Data,-3) Cells containing the value 3 or –3
=COUNTIF(Data,TRUE)
Cells containing logical TRUE
=COUNTIF(Data,TRUE)+COUNTIF(Data,
FALSE)
Cells containing a logical value (TRUE or FALSE)
=COUNTIF(Data,”#N/A”)
Cells containing the #N/A error value

Search JabSto ::

Custom Search