HOW TO USE SUMPRODUCT FUNCTION IN EXCEL

What is SUMPRODUCT FUNCTION??

Hi everyone, Today we are going to discuss SUMPRODUCT Function of Excel Sheet.In this article, We would learn how to use Sumproduct Function and how it works. Sumproduct Function is a Flexible Function used to calculate the sum of products of two or more arrays, like to calculate revenue we need to multiply our unit price with the Quantity sold and then add all the item’s products, these calculations can be done easily by using Sumproduct Function, Let us discuss how.

How to implement SUMPRODUCT Function??

Sumproduct Function is very simple to use and its Syntax is very easy to remember,

=Sumproduct(array 1, array 2,……..)

ARGUMENTS:

1. array 1: This is a mandatory field to pass through Sumproduct Function, It can be any column from your Table you want to get  Sumproduct of.The minimum number of arrays we can pass is one, in such case Sumproduct Function simply adds up all the components in the array.

2. array 2: We can supply 2 to 30 arrays as arguments according to the need.Here, A Sumproduct Function will first multiply array 1’s components to other corresponding array’s components and then sum it all, and return the value.

POINTS TO REMEMBER WHILE WRITING AN SUMPRODUCT FUNCTION:

• Sumproduct Function’s Arguments are arrays, that needs to be of same Dimensions, otherwise, the Sumproduct Function will return an error Value #VALUE!
• Non-Numeric Array Entries are treated to be as Zeroes by Sumproduct Function.

Let’s understand this by an Example:

Consider the Following table,

Here, we need to calculate total revenue generated, for this, we would apply Sumproduct Function, Let’s write SUMPRODUCT FUNCTION for this,

1. array 1: Our First Array is Quantity which consists 4 components, that can be written in parenthesis of Sumproduct Function as,

=Sumproduct (B2:B5,

2. array 2: Second array here is Price that also has same dimensions as Quantity Array, So we would specify this array in our Function as,

=Sumproduct(B2:B5, C2:C5)

NOTE: The range of array is defined by specifying the Column Name followed by the starting of the Row Number separating its end limit with a colon and specifying Column Name with the Row number of the last entry of the same Column.

Now, When you press Enter, you will get the total Revenue generated,

This is How we can calculate SUMPRODUCT of up to 30 arrays using just one Function.

How SUMPRODUCT FUNCTION Works??

Now, That we have learned to use SUMPRODUCT Function, let us see how it works internally,

What SUMPRODUCT Actually do is this,
=Sumproduct(array 1* array 2)
OR
=Sumproduct(B2:B5 * C2:C5)
OR
=2*1000+4*250+4*100+2*50

All of these gives the same result, though it seems very easy to deal with two arrays SUMPRODUCT, it is very useful for calculations of Complex Tables when we need to multiply more than two or three arrays and get the sum of them.And you also can use “DIVISION” operator in place of “MULTIPLICATION” like this,

SUMPRODUCT(B2:B11/C2:C11) ≡ (1÷2)+(2÷4)+(3÷6)+ … + (10÷20)

So, Don’t you think it’s very easy and useful tool in Excel, Share this with your Friends and Colleagues who are facing difficulty in dealing with SUMPRODUCT Function, and help them out.Practice the same for Complex tables, like for more than 3-4 arrays, so that you can work like a pro with Excel.If you have any question regarding this post then you can freely post it to us, We’ll be happy to answer you.