Leading the way in Microsoft Office Development

SUMPRODUCT(array1 [,array2] [,array3] [, …])

 -

SUMPRODUCT worksheet function

Returns the sum of the product of one or more arrays of values.

This basically means that the corresponding items in the arrays are multiplied together and then added up.

All the cell range arguments must be the same size and it is not possible to include whole columns.

This function can be used to multiple up to 30 cell ranges or arrays.

Simple Examples

The following two examples show this function in its most simple form.

The formula in cell "B7" is multiplying the two columns of numbers and then adding them up.

This function is equivalent to the formula in cell "B8".

Any text that may be included in your arrays will be ignored.

The following example shows what the result is when one of the arrays contains some text.

In this case the product of anything multiplied by text is zero.

Removing the Subtotal Column

The following table contains information about how many people were working on a particular day and how long they were working for.

Lets suppose we want the total number of hours worked in this table.

We could obtain a subtotal for each day (column "C") and then total these cells (cell "F8") or we could just use the SUMPRODUCT() function.

This function takes cell ranges (or arrays) as its arguments and multiplies the corresponding elements together and then adds all the results.

The function used in cell "C12" is equivalent to the formula displayed in cell "D14".

Things to Remember

If you are using anything other than Excel 2007 it is not possible to refer to an entire column using this function.