“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.
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 nonnumeric
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.