5.13
Using
functions
Overview
A
formula
in
Microsoft
Excel
performs
calculations
on
values
in
your
worksheet.
Typically,
formulas
perform
calculations
on
all
the
values
in
a
given
range.
However,
what
if
you
want
Excel
to
change
your
formula
if
a
certain
condition
is
true,
or
what
if
you
want
to
include
only
the
values
that
meet
certain
conditions
in
the
calculation?
For
example,
you
might
want
to
track
the
orders
placed
by
your
salespeople
and
then
summarize
the
sales
for
each
salesperson
without
reorganizing
your
data.
Or
you
might
want
to
determine
the
bonus
amount
awarded
for
each
sale,
based
on
the
total
invoice
amount.
When
you
want
formulas
to
perform
conditional
tests,
you
can
use
conditional
formulas
in
Excel.
A
list
of
sales
by
salesperson.
Use
conditional
formulas
to
calculate
bonus
percentages
and
to
summarize,
per
salesperson,
the
number
of
orders
placed
and
the
total
amount
invoiced
during
a
given
period.
Excel
includes
three
worksheet
functions
that
calculate
results
based
upon
conditions.
To
count
the
number
of
occurrences
that
a
specific
value
appears
in
a
range
of
cells,
use
the
COUNTIF
worksheet
function.
To
calculate
a
total
amount
based
on
a
single
condition,
use
the
SUMIF
worksheet
function.
To
return
one
of
two
values —
such
as
the
bonus
percent
amount —
use
the
IF
worksheet
function.
If
you
are
not
familiar
with
worksheet
functions,
the
Conditional
Sum
Wizard
that
can
assist
you
with
creating
formulas
that
calculate
sums
based
on
conditions.
The
SUMIF
and
COUNTIF
worksheet
functions
Suppose
that
you
want
to
create
a
summary
of
that
shows,
for
each
salesperson,
the
total
number
of
orders
placed
and
the
total
amount
invoiced
for
a
given
period.
To
count
the
number
of
orders
placed,
use
the
COUNTIF
worksheet
function.
To
calculate
the
total
amount
invoiced,
use
the
SUMIF
worksheet
function.
COUNTIF
The
COUNTIF
worksheet
function
counts
the
number
of
orders
placed
for
each
salesperson.
COUNTIF
has
two
arguments:
the
range
to
be
checked
and
the
value
to
check
for
within
the
range
(the
criteria).
=COUNTIF(range,criteria)
For
Buchanan,
the
function
(in
cell
B32)
looks
like
this:
=COUNTIF(A2:A26,A32)
The
function
counts
the
number
of
times
the
name
in
cell
A32
(the
criteria
argument)
appears
in
the
Salesperson
list
(A2:A26,
the
range
argument).
SUMIF
The
SUMIF
worksheet
function
calculates
the
total
amount
invoiced
for
each
salesperson.
The
SUMIF
worksheet
function
checks
for
a
value
within
a
range
and
then
sums
all
the
corresponding
values
in
another
range.
SUMIF
has
three
arguments:
the
range
to
be
checked,
the
value
to
check
for
within
the
range
(the
criteria),
and
the
range
containing
the
values
to
be
summed.
=SUMIF(range,criteria,sum_range)
For
Buchanan,
the
function
(in
cell
C32)
looks
like
this:
=SUMIF(A2:A26,A32,B2:B26)
The
formula
checks
for
the
text
in
cell
A32
(the
criteria
argument)
in
the
Salesperson
list
(A2:A26,
the
range
argument)
and
then
sums
the
corresponding
amounts
from
the
Total
Invoice
column
(B2:B26
the
sum_range
argument).
The
IF
worksheet
function
Suppose
that
your
company
determines
sales
bonuses
on
a
sliding
scale,
awarding
either
10
percent
or
15
percent,
based
on
the
invoice
amount.
To
determine
which
one
of
two
values
to
use,
based
on
a
condition
that
is
either
true
or
false,
use
the
IF
worksheet
function.
The
IF
worksheet
function
returns
a
bonus
of
either
10%
or
15%,
based
on
the
invoice
amount.
The
IF
worksheet
function
checks
a
condition
that
must
be
either
true
or
false.
If
the
condition
is
true,
the
function
returns
one
value;
if
the
condition
is
false,
the
function
returns
another
value.
The
function
has
three
arguments:
the
condition
you
want
to
check,
the
value
to
return
if
the
condition
is
true,
and
the
value
to
return
if
the
condition
is
false.
=IF(logical_test,value_if_true,value_if_false)
For
Suyama's
$8,000
invoice,
the
function
(in
cell
C4)
looks
like
this:
=IF(B4<10000,10%,15%)
If
the
invoice
amount
is
less
than
$10,000
(the
logical_test
argument),
the
bonus
is
10
percent
(the
value_if_true
argument).
If
the
invoice
amount
is
$10,000
or
greater,
the
bonus
is
15
percent
(the
value
_if_false
argument).
The
Conditional
Sum
Wizard
Suppose
you
have
multiple
conditions
you
want
to
check.
The
Conditional
Sum
Wizard
can
create
the
conditional
formulas
for
you.
The
Conditional
Sum
Wizard
quickly
builds
formulas
that
contain
multiple
conditions.
You
can
use
the
mouse
to
define
the
conditions
and
then
the
wizard
adds
the
formulas
to
your
worksheet.
In
the
wizard,
you
specify
the
location
of
the
list,
the
condition
to
check,
and
the
location
for
the
result.
In
the
preceding
example,
you
are
summing
the
invoice
amounts
for
Buchanan's
sales
that
were
greater
than
$10,000.
The
wizard
then
creates
an
array
formula
that
calculates
the
result
for
you.
If
you
need
to
change
the
condition,
you
can
use
the
wizard
again
and
replace
the
results
in
the
location
you
originally
specified.
The
Conditional
Sum
Wizard
is
an
add-in
program
supplied
with
Excel.
  
| 
