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