Microsoft Office Tutorials and References
In Depth Information
allow matches based on strings contained in text. You might also notice that the CRITERIA
and SUMRANGE arguments in these formulas are partially relative references. This allows the
formulas to be filled across for each region column and then filled down for the “south” and
“west” summar y rows.
The RANGE argument is the range of cells you want evaluated.
The CRITERIA argument is the criteria in the form of a number,
expression, or text that defines which cells will be added.
It is important to note that the RANGE argument cannot be a calculated array, unlike many
functions that do not differentiate between a worksheet range and an array. Whatever you
decide to use in this argument, Excel must be able to convert it into a Boolean expression
that defines the criteria.
The SUMRANGE argument is the actual cells to sum. The cells in
SUMRANGE are summed only if their corresponding cells in range
match the criteria. If SUMRANGE is omitted, the cells in range are
The SUMIF function
can use wildcard
The SUMPRODUCT function multiplies corresponding components in the given arrays, and
returns the sum of those products.
The ARRAY arguments must have the same dimensions. If they do not, SUMPRODUCT returns an
error value. If there are entries in an array that are nonnumeric, SUMPRODUCT treats them as if
they were zeros.