Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
Counting cells with the COUNTIF function
Excel’s COUNTIF function is useful for single-criterion counting formulas. The COUNTIF function
takes two arguments:
h range: The range that contains the values that determine whether to include a particular
cell in the count
h criteria: The logical criteria that determine whether to include a particular cell in the
count
Table 7-2 contains 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 flexible. You
can use constants, expressions, functions, cell references, and even wildcard characters (* and ?).
Table 7-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 and
nothing else (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
=COUNTIF(Data,”>”&AVERAGE
(Data)+STDEV(Data)*3)
Returns the number of values exceeding three standard deviations
above the mean
=COUNTIF(Data,3)+COUNTIF
(Data,-3)
Returns the number of cells containing the value 3 or –3
=COUNTIF(Data,TRUE)
Returns the number of cells containing logical TRUE
=COUNTIF(Data,TRUE)+COUNTIF
(Data,FALSE)
Returns the number of cells containing a logical value (TRUE or
FALSE)
=COUNTIF(Data,”#N/A”)
Returns the number of cells containing the #N/A error value
 
Search JabSto ::




Custom Search