SumProduct Function

In Excel SumProduct function multiplies the values in the corresponding cells in the arrays and returns the sum of the previous results

Syntax –

=SumProduct(array1, array2,….array_n)

array1, array2,…array_n is the range in which your data is placed in corresponding cells like below

	A	B
1	array1	array2
2	2	34
3	3	32
4	4	40
5	5	56
6	4	63
7	2	24
8	4	42
9	5	13

Here array1 is the range ($A$2:$A$9) whereas array2 is ($B$2:B$9)

Now we apply the SumProduct formula on these

= SUMPRODUCT($A$2:$A$9,$B$2:$B$9)

The result of this formula is 1137 which is actually a multiplication of corresponding cells then doing sum of the previous result values like below

Array1	array2	A*B
2	34	=2*34
3	32	=3*32
4	40	=4*40
5	56	=5*56
4	63	=4*63
2	24	=2*24
4	42	=4*42
5	13	=5*13
		=SUM(C2:C9)

If the array provided in SumProduct does not have the same no of records, the function would return #VALUE! error

SumProduct with Conditional selection

This formula can be used in doing calculation of the selected records on the basis of condition also.

Suppose you need to calculate the amount of total banana's sold through below mentioned table 

	A	B	C
1	Product	Qty	Price
2	Banana	2	34
3	Banana	3	32
4	Apple	4	40
5	Apple	5	56
6	Lemon	4	63
7	Lemon	2	24
8	Banana	4	42
9	Apple	5	13

The formula for calculating the amount of banana’s –

= SUMPRODUCT(($A$2:$A$9="Banana")*($B$2:$B$9)*($C$2:$C$9))

Result = 332

Here in this formula we are using (*) sign instead of (,) comma & each range closed within the parenthesis

Lets understand this syntax

The formula would return 1(One) where it finds the condition matched and 0(Zero) where condition does not matched

Lets look at in the table below

	A	B	C
1	Product	Qty	Price
2	1	2	34
3	1	3	32
4	0	4	40
5	0	5	56
6	0	4	63
7	0	2	24
8	1	4	42
9	0	5	13

Because records 2,3 & 8 were having Banana word in the cells the return value of these cells are 1 and other cells returns 0

Now even if you wish apply SumProduct formula on these ranges the result will be 332 because Zero multiply by any value will return Zero