|
 |
|
|
 |
  |
 |
 |
|
 |
|
|
|
The
SUMIF() function returns sum total
of a range of values based on specific
criteria.
- The syntax for
the function is SUMIF (range ,
criteria, sum_range).
The
argument for the function "range" can
be a series of up to 255 values, a
referenced range of cells, an array
or a named range. In addition the function
can accepts logical values when typed
in directly.
The argument "criteria"
can be a number, expression (equation),
a cell reference or text describing which
cells are to be added by the function.
e.g. 11 , "11"
, "< 15", A2 or "oranges".
The argument "sum_range"
is the range of cells that are to be
summed by the function if they meet the
criteria.
The SUMIF
function evaluates the "range"
values to see if they meet the conditions
specified in "criteria".
If the conditions are met, the associated
values that appear in the sum_range
are added together.
- For
example: SUMIF({3,8,4,6,2,5,7},"<4",{10,20,30,40,50,60,70})
= 60.
This
is due to the fact that the only
values in the "range"
that meet the criteria are 3 and 2.
Therefore the sum of their associated
values in the "sum_range"
is 10 + 50 which equals 60.
|
How
to use the SUMIF() function:
- Type " =SUMIF( ".
- Enter
the reference
coordinates for"Values", "A2:A6".
- Type
a comma.
- Enter
the criteria surrounded
by quotation marks.
- Type a comma.
- Enter
the coordinates range for
"Data" ,
"B2:B6".
- Type " ) " then
press the "Enter" key.
|
|
|
|
Note: Referenced
cells or array elements that are empty,
contain text or logical values are ignored
by the SUMIF function. However cells with
a value of zero are included in the calculation
of the sum.
Wild card characters can be used to extend
the matching possibilities of the "criteria".
- For example
the ? can be used to find any single
character: SUMIF({"red",
"brown", "blue", "yellow"}, "?w",
{1,2,3,4}) = 6
as the words in the range "brown"
and "yellow"
both contain the letter w and the sum of
their associated values is 2+4 =
6.
- Or an *
can be used to match a sequence
of characters: SUMIF({"red","brown",
"blue", "yellow"}, "*wn",
{1,2,3,4}) = 2 as the only word
that has the letter sting "wn"
in it is brown and
the sum of the associated value
is 2.
How
to use the SUMIF() function
with wildcards:
- Type " =SUMIF( ".
- Enter
the reference coordinates
for the "range", "B2:B8".
- Type
a comma.
- Enter
the "criteria"
, "*ge".
- Type a comma.
- Enter
the coordinates for the
"sum_range" ,
- Type " ) " then
press the "Enter" key.
|
|
|
Note: if
you want to use and actual question
mark or asterisk as selection criteria
simply add a tide (~) before the
character. e.g SUMIF(A2:A10, "~?",
B2:B10).
In addition to wild cards the SUMIF
function can use a variety of functions
available in Excel to make the selection
criteria more sophisticated. For example:
SUMIF(A2;A10, TODAY(), B2:B10) returns
the sum of values that have the same
date as today.
How
to use the SUMIF() function
with other functions:
- Type " =SUMIF( ".
- Enter
the reference coordinates
for the "range", "B2:B8".
- Type
a comma.
- Enter
the "criteria"
, "TODAY()".
- Type a comma.
- Enter
the coordinates for the
"sum_range" ,
- Type " ) " then
press the "Enter" key.
|
|
|
The SUMIF function can be made to sum
value when one criteria OR another
criteria is met for example: Typing
this in to a cell "=SUMIF(A2;A10, "orange",
B2:B10), =SUMIF(A2;A10, "apple", B2:B10)"
will cause the function to return a
sum when either of the conditions
are met.
To
meet multiple conditions at the same
time, that is to sum only when criteria
1 AND criteria 2 are
met Use ""=SUMIF(A2;A10,
>250, B2:B10) -SUMIF(A2;A10, <1000,
B2:B10)" or
use the SUMIFS() function to make the
formula more concise. See the [The
SUMIFS function knowledgebase].
|
|
 |
 |
 |
|
