ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Guide to the SUMPRODUCT function in Excel 2007 and Excel 2010

Updated on September 14, 2013

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

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    • profile imageAUTHOR

      Robbie C Wilson 

      4 years ago

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

    • epbooks profile image

      Elizabeth Parker 

      5 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!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)