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