SUMPRODUCT

No Comments

“SUMPRODUCT”

Used for – “SUMPRODUCT” function in MS Excel is used to find the sum of two arrays after multiplication of corresponding values in the given arrays.
Syntax – SUMPRODUCT(array1, [array2], [array3], …)
             array1 – It is a array for which you wants the corresponding values get multiplied and then get added to give the result. It is a compulsory field and if you did not fill any value excel consider it as 0 and does not give any error.
            [array2] – It is same as of array1 and is an optional field.
            [array3] – Like wise [array2] this is also an optional field and is an array. 

Example – Following example will clear the SUMPRODUCT function:-

In below table column A, B, D & E contains some numeric values upto 5th row & row 6 contains alphabets and alphanumeric value and column G contains the “SUMPRODUCT” function with different arrays.

A
B
C
D
E
F
G
H
I
1
2
6

3
7

=SUMPRODUCT(A1:B5,D1:E5)


2
5
3

6
2

=SUMPRODUCT(A1:A5,B1:B5)


3
10
4

11
3

=SUMPRODUCT(A1:B5,D1:E2)


4
11
2

12
3

=SUMPRODUCT(A1:A5)


5
25
8

26
9

=SUMPRODUCT(A1:B6,D1:E6)


6
Excel
With

Ease
1

=SUMPRODUCT(A1:A6,B1:B6)


7






=SUMPRODUCT(A6:B6,D6:E6)



And result you get as under:-

A
B
C
D
E
F
G
H
I
1
2
6

3
7

1066


2
5
3

6
2

289


3
10
4

11
3

#VALUE!


4
11
2

12
3

53


5
25
8

26
9

1066


6
Excel
With

Ease
1

289


7






0



Here results in G1 come as 2*3+5*6+10*11+11*12+25*26+6*7+3*2+4*3+2*3+8*9.
And in G5 come as 2*3+5*6+10*11+11*12+25*26+0*0+6*7+3*2+4*3+2*3+8*9+0*1.

NOTE :- Please note that SUMPRODUCT has following limitation :-
·         you can add only 255 arrays whose corresponding values get multiplied and then get added.
·         All the arrays must have of equal size in terms on rows and column else it will results in #Value error.
The non-numeric values are treated as zero in this function.

Dear readers, after reading the Content please ask for advice and to provide constructive feedback Please Write Relevant Comment with Polite Language.Your comments inspired me to continue blogging. Your opinion much more valuable to me. Thank you.