Page 298 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 255
Creating Formulas That Count and Sum
=COUNTIF(Data,”*budget*”)
Returns the number of cells containing the text budget anywhere within the
text
Returns the number of cells containing text that begins with the letter A (not
=COUNTIF(Data,”A*”)
case sensitive):
=COUNTIF(Data,TODAY())
Returns the number of cells containing the current date
Returns the number of cells with a value greater than the average
=COUNTIF(Data,”>”&AVERAGE(Data))
Returns the number of values exceeding three standard deviations above
=COUNTIF(Data,”>”&AVERAGE(Data)+
the mean
STDEV(Data)*3)
=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)+
Returns the number of cells containing a logical value (TRUE or FALSE)
COUNTIF(Data,FALSE)
Returns the number of cells containing the #N/A error value
=COUNTIF(Data,”#N/A”)
Counting cells by using multiple criteria
In many cases, your counting formula will need to count cells only if two or more criteria are met. These 14
criteria can be based on the cells that are being counted or based on a range of corresponding cells.
Figure 14.2 shows a simple worksheet that I use for the examples in this section. This sheet shows sales
data categorized by Month, SalesRep, and Type. The worksheet contains named ranges that correspond to
the labels in row 1.
This workbook is available on the companion CD-ROM. The file is named multiple criteria
ON the CD-ROM counting.xlsx.
ON the CD-ROM
Several of the examples in this section use the COUNTIFS function, which is new to Excel
NEW FEATURE 2007. I also present alternative versions of the formulas, which should be used if you plan to
NEW FEATURE
share your workbook with others who don’t use Excel 2007.
FIGURE 14.2
This worksheet demonstrates various counting techniques that use multiple criteria.

255