Basic and advanced Sumproduct formula in excel with multiple criteria and conditions with examples

 How to use Sumproduct function in Excel with examples

Sumproduct is used to get total/sum of the products for the corresponding range of value or array. In fact, it performs multiplications plus it is also useful in addition, subtraction and division. 

It is used to perform various arithmetic operations as described below:

(1)Basic Sumproduct formula with example:

Syntax:

=Sumproduct(array_1, array_2, array_3, array_4,.......)

The sumproduct fuction uses following arguments:

• array_1 - This first argument is compulsory for which values you want to multiple and then add.I
• arra_2, array_3, array_4 - These are optional arguments. 
• It accepts up to 255 arguments.
• It will return an array if arguments are not of same ranges like =sumproduct(B2:B20, C2:CC4).

#Example 01:

Do enter the formula as =sumproduct(L5:L8, M5:M8) and finished with enter key

And you will get the result as;

(2)How to Use the Excel SUMPRODUCT Function for Subtraction:

To use sumproduct for subtraction, division or addition on array of values use the relevant arithmetic symbols like (/,+,-,) according to which operation you want to perform.

Let's see the example:

In the above example I just have performed subtraction operation to get the actual and final inventory available in the store via using formula as

=Sumproduct(L5:L8-M5:M8)

And, the answer is:

(3)Excel SUMPRODUCT Function for Multiplication:

As the above example shows type the formula =Sumproduct(M5:M8*N5:N8) (using the arithmentic sign of multiplication *)

And, the answer is 43500;

(4)Excel SUMPRODUCT Function for Division:

Now, you can easily do it at your own.

Enter the formula with arithmetic sign of division * as below:

=Sumproduct(M5:M8/N5:N8)