Sumproduct Function Explained

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
Sony 52 $1,200
Samsung 38 $980
LG 36 $1,150

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.

Syntax

SUMPRODUCT(array1,array2,array3, …)

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.

insert-function

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.

function-arguments

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.

=SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4)

tv-example

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.

excel-hint1

excel-hint2

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

sumproduct-rows

 Arrays in Multiple Columns

sumproduct-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
2010 Sony 25 $800
2010 Samsung 30 $900
2010 LG 20 $1,000
2011 Sony 30 $900
2011 Samsung 20 $1,000
2011 LG 15 $750
2012 Sony 25 $900
2012 Samsung 25 $900
2012 LG 30 $800

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.

=SUMPRODUCT((D2:D10*E2:E10)*(C2:C10=”Sony”))

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.

Multiple Conditions

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.

=SUMPRODUCT((D2:D10*E2:E10)*(C2:C10=”Sony”)*(B2:B10=2011))

Note the pairing of the brackets and use of asterisks. It should not be difficult to understand the logic.

Conclusion

You can use excel sumproduct function to do simple calculations quickly. But when you combine it with conditions, you can do a lot more.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>