Microsoft Office Tutorials and References
In Depth Information
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.
The COUNTIF function counts the number of cells within a range that meet
the given criteria. This function takes the following arguments:
range — This is the range of cells from which you want to count
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
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).