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”) .