Guide to the SUMPRODUCT function in Excel 2007 and Excel 2010
Using the SUMPRODUCT function in Excel 2007 and Excel 2010 to monitor inventory
Hi and welcome to my latest hub on Excel. Today, we are going to look at a very useful function indeed, SUMPRODUCT. It allows you multiply two or more (up to a maximum of 255) sets of data and then sum the products.
To illustrate how to use this function in a formula we will use the following examples.
- In the first part of today’s hub, we will use the example of my chain of five fruit shops across England to illustrate how to use SUMPRODUCT with two data ranges. In this example, we will calculate stock levels by multiplying the quantity and unit cost for each stock line in every shop and then summing those values.
- In the second part, we will use data from a sales team to illustrate how to use SUMPRODUCT with more than two data ranges. In our second example, we will calculate the overall wages for the team by multiplying each salesman's sales by their commission and their length of service bonus and then summing their individual wages.
Before we begin with our examples, there are two points to consider about the data that you are going to use in your SUMPRODUCT formulas:
- The data does not have to be contiguous (in one place on your worksheet) for Excel to be able to calculate the sum of all the products (result of two or more numbers being multiplied).
- To ensure that you are able to use SUMPRODUCT on your data without getting an error, the ranges you ask it to use in the formula must be the same shape. We will look at this in detail when we look at multiple data ranges in the second example.
I can use SUMPRODUCT to calculate the total value of my stock across all my shops as shown below:
What Excel does is:
- First it multiplies the quantity of each fruit by its unit cost to get the value of the stock for each type fruit for each of my five shops.
- These values are then added up to calculate the overall value of all of the different types of stock
Using SUMPRODUCT in a formula with two sets of data in Excel 2007 and Excel 2010
There are two ways that you can utilise SUMPRODUCT in your spreadsheets and how you use it depends very much on how your data appears in your workbook.
One of the most important things to always bear in mind when designing your formulas is that you should always keep the amount of manual data entry or data moving to an absolute minimum. This not only saves you a lot of time, but keeps errors to a minimum.
Always look to adapt your formulas to your data rather than adapting your data to fit formulas!
The first way we will use SUMPRODUCT is with the data illustrated above. We have our Quantities in one column and the Unit Costs in another. We multiply those together for each type of fruit in each shop and add that all up. The formula for this is:
Excel cleverly ignores all empty rows or rows with text in them.
In the figure below, I have shown how Excel calculates the result of SUMPRODUCT. The values in column D are the result of the cells in B and C for that row being multiplied. The value in D29 is the sum of all the values in D.
Now we are going to change the way the data is organised to illustrate another way to utilise SUMPRODUCT. The below figure shows the data re-organised to illustrate this:
The formula on this occasion is:
In this case, what Excel does is multiply I6 by M6, J6 by N6, K6 by O6, I7 by M7, J7 by N7 until it gets to K10 which it multiplies by O10 and then it finally adds up all of the results.
Using the SUMPRODUCT function in a formula with multiple data ranges
Excel allows you to use up to 255 data ranges in one SUMPRODUCT formula. To show how to use it with more than two data ranges, we will use the following example:
I have a team of seven salesmen. Each day they enter in their daily sales into the spreadsheet. In addition to this, their commission per sale is also recorded. The commission they receive fluctuates from day to day, based on their sales performance. Finally, each salesman receives a length of service bonus which is used to calculate their final pay.
Note: In order for SUMPRODUCT formulas to work, each of the data ranges need to be the same shape.
If we use the following formula to work out Philip’s pay:
We get a #VALUE error.
To fix this, we need to add four more columns for the length of service bonus
We now change the formula to include those extra columns and Excel gives us the answer we expect.
Note: You can hide any rows you have added and Excel will continue to calculate the formulas correctly. Use a cell reference for the hidden cells so for example, I would use =R37 for the hidden cells for Philip’s Length of Service Bonus so that when it changes, the other cells will automatically update.
Now we simply copy the cell C47 to add the pay for the week for each member of the sales team.
Finally, we can use SUMPRODUCT to calculate the overall pay for the entire team:
In today’s hub, we looked at the SUMPRODUCT function and how to use it in formulas. This very powerful and versatile function allows you to multiply data in a number of data ranges and then sum the results. We looked at two examples today:
The first was my chain of fruit shops. We used SUMPRODUCT to calculate the overall stock levels in all my shops. Excel took the quantity of each stock line, multiplied it by the unit cost and then summed all of those values.
In my second example, I looked at a team of salesmen and used SUMPRODUCT to calculate their overall wages. This example illustrated using multiple data ranges. In this example, to calculate the overall wage, I took each salesman’s daily sales, multiplied it by their commission and then by their length of service bonus and then summed the results for each member of the team.
The second example using multiple ranges illustrated a key point when working with SUMPRODUCT:
All data ranges have to be the same shape or SUMPRODUCT will return a #VALUE error.
I showed while working on the second example how to avoid this error.
Many thanks for reading this hub; I hope that you have found it useful and informative. Please feel free to leave any comments you may have below.
Are you likely to use SUMPRODUCT more in the future after reading this hub?
© 2013 Robbie C Wilson