Microsoft Office Tutorials and References

In Depth Information

Returns the number of cells with

a value greater than the average

=COUNTIF(Data,”>”&AVERAGE(Data))

Returns the number of values ex-

ceeding three standard deviations

above the mean

=COUNTIF(Data,”>”&AVERAGE(Data)+STDEV(Data)*3)

Returns the number of cells con-

taining the value 3 or –3

=COUNTIF(Data,3)+COUNTIF(Data,-3)

Returns the number of cells con-

taining or returning logical TRUE

=COUNTIF(Data,TRUE)

Returns the number of cells con-

taining or returning a logical value

(TRUE or FALSE)

=COUNTIF(Data,TRUE)+COUNTIF(Data,FALSE)

Returns the number of cells con-

taining the #N/A error value

=COUNTIF(Data,”#N/A”)

Counting cells that meet multiple criteria

In many cases, your counting formula will need to count cells only if two or more criteria are met. These criter-

ia can be based on the cells that are being counted or based on a range of corresponding cells.

Figure 7-2 shows a simple worksheet that I use for the examples in this section. This sheet shows sales figures

(Amount) categorized by Month, SalesRep, and Type. The worksheet contains named ranges that correspond to

the labels in row 1.