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,"*") ...

Get Office 2007 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial