Microsoft Office Tutorials and References

In Depth Information

**SUMPRODUCT**

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.

RANGE

The
CRITERIA
argument is the criteria in the form of a number,

expression, or text that defines which cells will be added.

CRITERIA

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

summed.

SUMRANGE

Figure 10.26

The
SUMIF
function

can use wildcard

characters.

SUMPRODUCT

The
SUMPRODUCT
function multiplies corresponding components in the given arrays, and

returns the sum of those products.

=SUMPRODUCT(array1,array2,array3, ...)

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.