SUMPRODUCT

Calculates the sum of the products of corresponding elements within one or more arrays or range of cells.

Syntax:

SUMPRODUCT(arrayOne, [arrayTwo], [arrayThree],…)

arrayOne is required, and is the first range of cells or array of values used in the calculation.

[arrayTwo], [arrayThree],… is optional, and is the additional range of cells or array of values used in the calculation. You can have from 2 to 255 additional arguments.

Example:

If arrayOne, found in B1:B3, contains numbers 85, 90, 78, and arrayTwo, found in C1:C3, contains 0.25, 0.35, 0.4:

SUMPRODUCT(B1:B3, C1:C3)

returns 83.95

This example uses SUMPRODUCT to find the weighted average score for a student.

	Assignment Number	Student's Assignment Scores	Assignment Weights
	A	B	C	D
1	1	85	0.25	83.95
2	2	90	0.35
3	3	78	0.4

Application:

Imagine you manage an office supply store and you have a spreadsheet tracking a recent order of different types of pens.

	Item	Quantity Ordered	Price per Pen
	A	B	C
1	Ballpoint Pen (Blue)	300	$0.75
2	Gel Pen (Black)	150	$1.20
3	Fountain Pen (Standard)	25	$8.50
4	Rollerball Pen (Red)	75	$1.50

To calculate the total cost of the entire order, you could manually multiply the quantity and price for each item and then add them all together: (300 * $0.75) + (150 * $1.20) + (25 * $8.50) + (75 * $1.50)

Using the SUMPRODUCT function, the formula is much more efficient:

SUMPRODUCT(B1:B4, C1:C4)

How the formula works:

The SUMPRODUCT function performs the following calculation: (300 * $0.75) + (150 * $1.20) + (25 * $8.50) + (75 * $1.50)

The function multiplies the corresponding values in the Quantity Ordered and Price per Pen columns and then sums the results to give you the total cost of the order.

The final result of this formula would be $730.00.

Result:

$730.00