Microsoft Office Tutorials and References

In Depth Information

**Summarizing data that meets specific conditions**

Just as the
COUNTIF
function counts the number of cells that meet a criterion and the

SUMIF
function finds the total of values in cells that meet a criterion, the
AVERAGEIF

function finds the average of values in cells that meet a criterion. To create a formula by using

the
AVERAGEIF
function, you define the range to be examined for the criterion, the criterion,

and, if required, the range from which to draw the values. As an example, consider a

worksheet that lists each customer’s ID number, name, state, and total monthly shipping bill.

If you want to find the average order value for customers from the state of Washington

(abbreviated in the worksheet as WA), you can create the formula
=AVERAGEIF(C2:C5,

“=WA”, D2:D5)
.

The
SUMIFS
,
AVERAGEIFS
, and
COUNTIFS
functions extend the capabilities of the
SUMIF
,

AVERAGEIF
, and
COUNTIF
functions to allow for multiple criteria. If you want to find the

sum of all orders of at least $100,000 placed by companies in Washington, you can create

the formula
=SUMIFS(D2:D5, C2:C5, “=WA”, D2:D5, “>=100000”)
.

The
AVERAGEIFS
and
SUMIFS
functions start with a data range that contains values that the

formula summarizes; you then list the data ranges and the criteria to apply to that range. In

generic terms, the syntax runs
=AVERAGEIFS(data_range, criteria_range1, criteria1[,criteria_

range2, criteria2...])
. The part of the syntax in square brackets (which aren’t used when you

create the formula) is optional, so an
AVERAGEIFS
or
SUMIFS
formula that contains a single

criterion will work. The
COUNTIFS
function, which doesn’t perform any calculations, doesn’t

need a data range—you just provide the criteria ranges and criteria. For example, you could

find the number of customers from Washington who were billed at least $100,000 by using

the formula
=COUNTIFS(C2:C5, “=WA”, D2:D5, “>=100000”)
.