Microsoft Office Tutorials and References

In Depth Information

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

AVERAGEIF
,
SUMIF
, 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(E3:E6, D3:D6, “=WA”, E3:E6, “>=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(D3:D6, “=WA”, E3:E6, “>=100000”)
.

In this exercise, you’ll create a conditional formula that displays a message if a condition

is true, find the average of worksheet values that meet one criterion, and find the sum of

worksheet values that meet two criteria.

SET UP
You need the PackagingCosts_start workbook located in your Chapter03

practice file folder to complete this exercise. Open the PackagingCosts_start

workbook, and save it as
PackagingCosts
. Then follow the steps.

1.
In cell
G3
, type the formula
=IF(F3>=35000, ”Request discount”, ”No discount

available”)
, and press Enter.

Excel accepts the formula, which displays
Request discount
if the value in cell F3 is

at least 35,000 and displays
No discount available
if not. The value
Request discount

appears in cell G3.

2.
Click cell
G3
, and drag the ill handle down until it covers cell
G14
.

Excel copies the formula in cell G3 to cells G4:G14, adjusting the formula to reflect

the cells’ addresses. The results of the copied formulas appear in cells G4:G14.