Excel Sumproduct is one of the most used and versatile function in excel. It multiplies the corresponding elements of two or more arrays and returns the sum of the products. If used properly, it can save you a lot of your time.
One of the most widely use of this function is to get the weighted average. With the below example, you will understand how to use sumproduct function for calculating weighted average.
Suppose we have to calculate the average selling price of flat panel television of a store. This store sells the TVs of three brands. Here is the summary:
|Brand||No of TV Sold||Selling Price|
So, what will we do? We will multiply the number of TVs sold with selling price for each brand and then divide by the total number of TVs to get the average selling price. That’s right. In this case, the SUMPRODUCT function can be handy to use.
As you can see, the syntax is really simple. You just need to define two or more arrays of numbers and the it will return you with the sum of the products of the corresponding items.
To insert this function, click on the insert function icon. In ‘insert function’ window, you may like to search the Sumproduct or select it from ‘Math & Trig’ category.
After you click OK, function argument window appears. Click inside the array1 textbox and make selection to the first array. After this, click inside the array2 textbox and select the second array. Click on OK to get the result.
In the example shown in the figure above, the function does the following:
(1*10 + 2*20 + 3*30 + 4*40 + 5*50)
So, the result is 550.
You can select up to 255 arrays for which you want to multiply and then add the products.
One important thing to note here is that all the arrays must have the same dimensions; otherwise you will get an error. It means if your first array has 5 numbers; second and subsequent arrays must also have 5 numbers for this formula to work.
And if you have a non-numeric value inside the array, the formula will treat it as zero.
Weighted Average with SUMPRODUCT formula
Coming back to our first example of calculating the average selling price of TVs; you can use the following formula.
Although, for beginners it is easier to use ‘insert function’ window and make selection, you can get your work done faster by directly typing the formula in the cell where you want the result.
For example, if we wish to get our result in cell C6; all we have to do is to start typing sumproduct after a “=” (equals) sign. Microsoft excel will automatically start giving you hints that you need to do next.
So, you can directly type the above formula in the cell. Click on enter and you get your result, which in this case is $1119.36.
Arrays need not to be in Columns
In almost every example, you will find that the arrays are arranged in columns. This may give you impression that the arrays must be arranged in columns. In-fact, you can arrange your arrays in both rows and columns and even in multiple rows and columns. Only thing you need to make sure here is that all the arrays must have the same dimension (same number of values).
See the following examples.
Arrays in Rows
Arrays in Multiple Columns
This was about the simplest use of this function. Once you are through, we will move on to some more examples.
Sumproduct with Logical Functions
When combined with logical conditions, Sumproduct becomes more powerful. We need to adjust the formula a little to achieve that. I will straightway take you through an example to understand how it works.
|Year||Brand||No of Sales||Selling Price|
From this table, we are interested in finding the total selling value of only Sony products in three years.
Type the following formula in the cell where you want to get the result.
Press enter and you get the result which is $69500.
There are a few important things to note here:
- In place of “,” (commas); we are using “*” (asterisks) between the arrays.
- All the arrays are put inside one bracket.
- The condition is put under separate bracket and there is an “*” (asterisk) between ranges and condition.
- You can’t use the insert function here. You need to type manually.
- You can throw multiple conditions at the same time as per your requirement.
In the above example, the formula checks if the condition (C2:C10=”Sony”) is met. In case of TRUE, excel assigns a value 1 otherwise 0.
If in the example above, we need to know the selling value of only Sony products in year 2011, we can put an additional condition like this.
Note the pairing of the brackets and use of asterisks. It should not be difficult to understand the logic.
You can use excel sumproduct function to do simple calculations quickly. But when you combine it with conditions, you can do a lot more.