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:

An example of how to use the SUMPRODUCT function in formulas in Excel 2007 and Excel 2010.
An example of how to use the SUMPRODUCT function in formulas in Excel 2007 and Excel 2010. | Source

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:

=SUMPRODUCT(B6:B26,C6:C26)

Example of how to use SUMPRODUCT in a formula in Excel 2007 and Excel 2010.
Example of how to use SUMPRODUCT in a formula in Excel 2007 and Excel 2010. | Source

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.

Illustrating how SUMPRODUCT is calculated in Excel 2007 and Excel 2010.
Illustrating how SUMPRODUCT is calculated in Excel 2007 and Excel 2010. | Source

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:

Showing how SUMPRODUCT can work with different data layouts in Excel 2007 and Excel 2010.
Showing how SUMPRODUCT can work with different data layouts in Excel 2007 and Excel 2010. | Source

The formula on this occasion is:

=SUMPRODUCT(I6:K10,M6:O10)

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.

Another example of using SUMPRODUCT in a formula in Excel 2007 and Excel 2010.
Another example of using SUMPRODUCT in a formula in Excel 2007 and Excel 2010. | Source

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.

Example of data stored in multiple ranges which will be used to illustrate SUMPRODUCT in Excel 2007 and Excel 2010.
Example of data stored in multiple ranges which will be used to illustrate SUMPRODUCT in Excel 2007 and Excel 2010. | Source

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:

=SUMPRODUCT(C37:G37,K37:O37,R37)

We get a #VALUE error.

To fix this, we need to add four more columns for the length of service bonus

Always ensure that the data ranges are the same shape when using SUMPRODUCT in Excel 2007 and Excel 2010.
Always ensure that the data ranges are the same shape when using SUMPRODUCT in Excel 2007 and Excel 2010. | Source

We now change the formula to include those extra columns and Excel gives us the answer we expect.

=SUMPRODUCT(C37:G37,K37:O37,R37:V37)

SUMPRODUCT formula modified to include the added cells in Excel 2007 and Excel 2010.
SUMPRODUCT formula modified to include the added cells in Excel 2007 and Excel 2010. | Source

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:

SUMPRODUCT calculating the overall pay for the entire sales team in Excel 2007 and Excel 2010.
SUMPRODUCT calculating the overall pay for the entire sales team in Excel 2007 and Excel 2010. | Source

Conclusion

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?

See results without voting

© 2013 Robbie C Wilson

More by this Author


2 comments

profile image

Robbie C Wilson 3 years ago Author

Thanks for your kind comment. I am glad that you found my hub useful.


epbooks profile image

epbooks 3 years ago from Las Vegas, NV

Fantastic and useful information here. I love excel and it has so many different functions to offer. Your hubs are a great help to those who do not know how powerful Excel can be. Voted up and shared!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working