Users are constantly searching for ways to tally certain types of entries in a range or across several ranges, so this article is devoted to Excel counting techniques. See the table for a summary of methods.

The count, counta, and countblank functions are straightforward, so I'll go directly to the more complex countif. This function takes two arguments: the range that holds the data to be counted, and the criterion used to determine whether a cell should be included in the count.

The examples below demonstrate various uses for the countif function. They assume that you have a range named data. Also, note that the second argument for the countif function can refer to a cell that contains the search criterion.

To count the number of cells that contain the value 12, enter =countif(data,12).

To tally the number of cells containing 1 or 12, type =countif(data,1)+countif(data,12).

To count the number of cells that contain a value from 1 to 10, use =countif (data,">=1")-countif(data,"<=10").

To count the number of cells containing a certain word ù say, yes ù type =countif (data,"yes").

Finally, if you want to tally the number of cells containing any text, use =countif(data,"*").

Countif is useful when you have a single criterion. For more complex comparisons, you may want to use dcount. This function requires that your data be set up as an Excel database (with field names in the first row), and that a separate criteria range specify the conditions.

The worksheet illustrated shows an example of a simple database in A1:C13 and a criteria range in E1:G2. The formula in cell E4, which returns the number of sprockets sold in January, is =dcount(A1:C18,,E1:G2).

Caption:Complex counting requires complex formulas, such as dcount, which tallies records in a database

The formula will yield a different value whenever you change the values in the criteria range. Also, the criteria range can handle logical "or" operations when you use additional rows.

If none of the standard techniques fits the bill, construct an array formula, which lets you work with multiple cells in a range. To create an array formula, press <Ctrl>-<Shift>-<Enter> after typing the formula.

To tally the number of values in a range, enter =sum(if(isnumber(data),1,0)).

To count the number of cells that contain an error, try =sum(if(iserr(data),1,0)).

The ultimate in counting tools, a pivot table lets you summarise data in just about any way. Select DataûPivotTable to start the PivotTable Wizard.