Microsoft Office Tutorials and References

In Depth Information

**Syntax**

After you enter this formula, you can build a table of the unique regions

in column A, copy the formula down column B, and quickly have a summary

table built with the help of COUNTIF.

Syntax

=COUNTIF(range,criteria)

The COUNTIF function counts the number of cells within a range that meet

the given criteria. This function takes the following arguments:

•
range

range
—
This is the range of cells from which you want to count

cells.

•
criteria

criteria
—
This is the criteria in the form of a number, an expression,

or text that defines which cells will be counted. For example, criteria

can be expressed as 32, "32", ">32"", or "apples". Any criteria that

contains text or a mathematical operator must be enclosed in quotes.

For numeric criteria, the quotes are not required.

•
You can use the wildcard characters question mark (?) and asterisk

(*) in criteria. A question mark matches any single character; an as-

terisk matches any sequence of characters. If you want to find an ac-

tual question mark or asterisk, you need to type a tilde (~) before the

character.

After you have mastered COUNTIF, it is easy to master SUMIF and

AVERAGEIF. In most cases, the SUMIF function adds one new argument. Where-

as COUNTIF would ask for a range of data and then the value to look for in

that range, SUMIF usually needs three arguments: SUMIF asks for a range

of data, the value to look for in that range, and then another range of data

to be summed when a match is found.

In
Figure 11.32
, B11:B5011 contains the range to search. Cell A2 contains the

value for which to search. When Excel finds a matching value in column B,

you want Excel to return the corresponding cell from the Revenue column

in H11:H5011. Most people would write

=SUMIF($C$11:$C$5011,A2,$H$11:H$5011) to do this. It turns out that Excel

forces the third argument to have the same shape as the first argument. If

you happen to accidentally specify H11:H4011, Excel ignores your range and

uses H11:H5011 because it is the same shape as the first argument. Thus, it is

sufficient to write the formula as =SUMIF($C$11:$C$5011,A2,$H$11).