How To Use The Excel Sumproduct Function, Excel Sumproduct Function With Multiple Criteria

SUMPRODUCT is a function in Excel that multiplies range of cells or arrays and returns the sum of products. It first multiplies then adds the values of the input arrays. It is a ‘Math/Trig Function’. It can be entered as a part of a formula in a cell of a worksheet. It is a very resourceful function which can be used in many ways depending on the requirement of the user.

Đang xem: How to use the excel sumproduct function

Syntax

The syntax of the SUMPRODUCT function is –

=SUMPRODUCT(array1, )

array1 – this parameter is the first array or range that will be multiplied then addedarray2….array_n – these parameters are the second and onwards arrays or ranges that will be multiplied then added. These are optional parameters.

Xem thêm: Từ Nghiệm Suy Ra Phương Trình Bậc Hai, Dạng 4: Ứng Dụng Của Định Lí Vi

How does SUMPRODUCT work

Let us look at a very basic example to try and understand how the SUMPRODUCT function works. Suppose we have 2 arrays – {3,5;6,1} and {4,2;7,8}. If we use SUMPRODUCT function on these two arrays, the formula will look like –

=SUMPRODUCT({3,5;6,1}, {4,2;7,8})

The output upon entering this formula in a cell will be 72. Let us understand the maths behind this result. The SUMPRODUCT function calculates the product as follows –

=(3*4)+(5*2)+(6*7)+(1*8)

= 12+10+42+8

=72

As we can see, the SUMPRODUCT function multiplies and then adds the input arrays. The input arrays can also be written in terms of cell reference. So, if the array {3,5;6,1} is put in cells A3:B4 and {4,2;7,8} is put in D4:E5, then the function will look like –

=SUMPRODUCT(A3:B4, D4:E5)

When we put in this formula in a cell, the result will be the same. The return value of the function will be 72 in this case as well.

Xem thêm: Tuyển Tập Các Dạng Đề Văn Nghị Luận Lớp 9 ? Các Đề Văn Nghị Luận Xã Hội Lớp 9

Conclusion

We have been introduced to the SUMPRODUCT function in this article. Then we looked at how this function works as well as its versatility. This function can be used in a large number of cases. It handles arrays in a simple manner and is easy to use once you understand how the function works.