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


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 –


= 12+10+42+8


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 –


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



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.

Xem thêm bài viết thuộc chuyên mục: Excel