Skip to main content

How to Use the SUMPRODUCT Function in Excel

Joshua earned an MBA from USF and he writes mostly about software and technology.

The SUMPRODUCT function of Excel can multiply and sum up to 255 times with data from two arrays (ranges). This function is common for its use in optimization problems.

The SUMPRODUCT function of Excel can multiply and sum up to 255 times with data from two arrays (ranges). This function is common for its use in optimization problems.

Here the SUMPRODUCT function is discussed with a discussion of its purpose and its syntax. This will be followed by a simple example and a demonstration to show how it can be in inserted into a spreadsheet.

The Purpose of the SUMPRODUCT Function

The SUMPRODUCT function will return the sum of the products of ranges or arrays that correspond to each other. In other words, data from each cell in multiple ranges or arrays of the same size are multiplied together and then summed. The result appears in one cell.

If the two ranges used were A1:A2 and B1:B2, the result mathematically would come from the following equation:

[(A1)(B1)] + [(A2)(B2)]

The SUMPRODUCT Function Syntax

The SUMPRODUCT function needs to be inputted into a cell like a formula to work. Manually add the SUMPRODUCT formula with the following steps:

1. First, a cell needs clicked in. Type =SUMPRODUCT(

2. After the open parenthesis, the data (range, named range, cell references) that needs to be multiplied and summed needs to be entered with data inputs separated by commas.

3. This is then followed by a closing parenthesis. After the formula is created, the enter button can be pressed to display a result.

Below in bold is the syntax of the function with explanations of each argument.

=SUMPRODUCT(array1, array2)

The SUMPRODUCT function syntax has the following arguments:

array1 At least 1 array is required. This first array argument is the component that will be multiplied and then added.

array2 Any additional arrays added are optional. There can be 2 to 255 arrays in this function where each array will multiply and then add.

SUMPRODUCT Example: Finding Total Order Cost

Here we illustrate a simple example of how the SUMPRODUCT function can be used. Suppose that you have an order of multiple items in a spreadsheet. If you have the unit price, and the quantity for the order you should be able to calculate the total order cost with this function.

Scroll to Continue

Take for instance the example below.

how-to-use-the-sumproduct-function-in-excel

The SUMPRODUCT function can be used to find the total cost of each item above and display the sum of all of those individual costs. The function is shown below after selecting each array of data.

how-to-use-the-sumproduct-function-in-excel

Here the result is displayed in the cell.

how-to-use-the-sumproduct-function-in-excel

Inserting the SUMPRODUCT Function

The SUMPRODUCT function can be inserted into a cell with the use of an insert tool from the formulas tab with the following steps:

  1. To start this process, select and click on a cell.
  2. Click on the formulas tab and click on the "Math & Trig" button.
  3. The SUMPRODUCT selection needs to be chosen from the drop-down menu.
To insert the SUMPRODUCT function from the formulas tab you must first navigate to Formulas→Math & Trig→SUMPRODUCT.

To insert the SUMPRODUCT function from the formulas tab you must first navigate to Formulas→Math & Trig→SUMPRODUCT.

After the function arguments window appears, all arrays that need to be multiplied and summed can be added to the fields. The results can be been after arguments are added. After the arrays are added, the OK button can be selected.

The Function Arguments Window

In this window, the function arguments can be added to the function. After an argument field is clicked, instructions will appear and data can be entered.

In this window, the function arguments can be added to the function. After an argument field is clicked, instructions will appear and data can be entered.

References

Microsoft. (n.d.). SUMPRODUCT function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e.

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

How to Remove Errors in Excel With the IFERROR Function

How to Use the VLOOKUP Function in Excel

Converting Measurement Units: Using the CONVERT Function

To learn more about using functions in Excel I recommend The Excel 2019 Bible book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder

Related Articles