O'Reilly logo

Office 2007 Bible by Lisa A. Bucki, Gavin Powell, Michael R. Irwin, Peter G. Aitken, Michael R. Groh, Cary N. Prague, Faithe Wempen, Herb Tyson, John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Advanced Counting Formulas

Most of the basic examples I presented earlier in this chapter use functions or formulas that perform conditional counting. The advanced counting formulas that I present here represent more complex examples for counting worksheet cells, based on various types of criteria.

Note

Some of these examples are array formulas.

Counting cells by using the COUNTIF function

Excel’s COUNTIF function is useful for single-criterion counting formulas. The COUNTIF function takes two arguments:

  • range: The range that contains the values that determine whether to include a particular cell in the count

  • criteria: The logical criteria that determine whether to include a particular cell in the count

Table 17-2 lists 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 17-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,"*") ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required