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

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

array1The first array.
array2(Optional) The second array.
array3(Optional) The third array.

Remarks

* For an illustrated example refer to the page under Advanced Functions.
* If the array arguments have different dimensions, then #VALUE! is returned.
* Any non numeric arguments are treated as zero.
* You can have up to 30 arguments.
* You can use the COUNTIFS function to return the number of non blank cells that satisfies multiple conditions
* You can use the SUMIFS function to return the total value of the numbers that satisfies multiple conditions.
* For the Microsoft documentation refer to support.office.com

 ABC
1=SUMPRODUCT(1,2,3) = 616
2=SUMPRODUCT(B1:B3) = 627
3=SUMPRODUCT({1,2,3,4}) = 1038
4=SUMPRODUCT(1,2,3,4) = 2449
5=SUMPRODUCT(B1,B2,B3,B4) = 24510
6=SUMPRODUCT({1,2},{3,4}) = 11  
7=SUMPRODUCT({1,2}*{3,4}) = 11  
8=SUMPRODUCT({1,2,3},{4,5,6}) = 32  
9=SUMPRODUCT({1,2,3}*{4,5,6}) = 32  
10=SUMPRODUCT(B1:B2,C1:C2) = 20  
11=SUMPRODUCT(B1:C2,B3:C4) = 122  
12=SUMPRODUCT({1,6;2,7},{3,8;4,9}) = 122  
13=SUMPRODUCT(MMULT({1,2;3,4},{1,2;3,4})) = 54  
14=SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7}) = #VALUE!  
15=SUMPRODUCT((A1:A20>=5)*(A1:A20<=10)*A1:A20)  

1 - What is (1 + 2 + 3).
2 - This is equivalent to 1.
3 - What is (1 + 2 + 3 + 4).
4 - What is (1 * 2 * 3 * 4).
5 - This is equivalent to 4.
6 - What is (1*3) + (2*4).
7 - This is equivalent to 6.
9 - What is (1*4) + (2*5) + (3*6).
10 - This is equivalent to 9.
12 - What is (1*6) + (2*7).
13 - What is (1*3) + (2*4) + (6*8) + (7*9).
14 - This is equivalent to 13.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top