3 Examples to Master SUMPRODUCT in Excel

You might be familiar with the essential function like SUM and PRODUCT in Excel, but there is also a remarkable function called SUMPRODUCT. The SUMPRODUCT function in Excel is a versatile tool that empowers users to easily perform complex calculations.

SUMPRODUCT in Excel returns the sum of the products of corresponding ranges or arrays. It can be used to calculate the mean salary of employees, the average score on an exam, or the mean selling price of a company’s inventory, total investment value, etc.

In this article, we will explore the following topics in detail –

Download the Excel Workbook below to follow along and understand how to use SUMPRODUCT in Excel – download excel workbookSUMPRODUCT-in-Excel.xlsx

 

Introduction to SUMPRODUCT in Excel

The SUMPRODUCT in Excel is a versatile tool that performs the task of calculating the total sum derived from the products of corresponding components within given ranges or arrays. The syntax of SUMPRODUCT in Excel is –

=SUMPRODUCT(array1, [array2], [array3], …)

• array1 -The first array argument whose components you want to multiply and then add. Required.
• array2, array3, … – Array arguments 2 to 255 whose components you want to multiply and then add. Optional.

Please Note – The dimensions of the arrays should be the same or else this function will return an error.

Let us look at an example to understand better.

 

#1 – Basic Example

Suppose you have a dataset containing the quantities of different items sold and their respective prices. You want to calculate the total revenue generated from these sales. Here’s where SUMPRODUCT comes to your rescue.

STEP 1: Enter the SUMPRODUCT function.

=SUMPRODUCT

STEP 2: Enter the first argument i.e. array1. Here, it is the range containing the quantity of items sold (B2:B9).

=SUMPRODUCT(B2:B9

STEP 3: Enter the second argument i.e. array 2. Here, it is the range containing the unit price of the items sold (C2:C9).

=SUMPRODUCT(B2:B9,C2:C9)

Let’s evaluate this formula –

=SUMPRODUCT(B2:9,C2:C9)

=SUMPRODUCT({300;250;150;200;100;50;80;70},{10;5;20;30;15;50;25;40}) 

=SUMPRODUCT({3000,1250,3000,6000,1500,2500,2000,2800})

=22050

Excel will first multiply each item in array1 with the corresponding item in array2 and then simply sum the result. The SUMPRODUCT function simplifies what would otherwise involve multiple steps, making it a valuable asset for straightforward calculations.

 

#2 – SUMPRODUCT with other Arithmetic Operators

While multiplication is the default operation, addition, subtraction, and division can also be applied. Utilize SUMPRODUCT in the usual manner, but instead of using commas to separate array arguments, use the desired arithmetic operators (*, /, +, -). For example, to use division with 2 arrays –

=SUMPRODUCT(array1 / array2)

Suppose, we have a dataset with the total sales amount and unit process of different items sold. We want to know the total number of items sold based on the data provided. To do this, we need to first divide the total sales amount by the unit price for each item and then simply add the result to get the total quantity sold.

Instead, we can also use the SUMPRODUCT function by replacing the comma with the division sign to get the result –

STEP 1: Enter the SUMPRODUCT function.

=SUMPRODUCT

STEP 2: Enter the first argument i.e. array1. Here, it is the range containing the total sales amount (B2:B9).

=SUMPRODUCT(B2:B9

STEP 3: Enter the division operator (/).

=SUMPRODUCT(B2:B9/

STEP 4: Enter the second argument i.e. array 2. Here, it is the range containing the unit price of the items sold (C2:C9).

=SUMPRODUCT(B2:B9/C2:C9)

=SUMPRODUCT(B2:B9/C2:C9)

=SUMPRODUCT({3000;1250;3000;6000;1500;2500;2000;2800}/{10;5;20;30;15;50;25;40})

=SUMPRODUCT({300,250,150,200,100,50,80,70})

=1200

Excel returns the total number of units sold by dividing the sales amount by the unit price and then adding the result.

 

#3 – Weighted Averages

SUMPRODUCT can also be used to calculate weighted averages in different scenarios.

Suppose, you have invested in a portfolio of stocks, and you want to calculate the weighted average return for your investments. You have the total amount invested and the corresponding returns in percentage for all the stocks. Using the SUMPRODUCT function, you can determine the overall weighted average return of your portfolio.

You will have to first use the SUMPRODUCT function to calculate the total return (in $) of the investment by multiplying the amount invested with the return% and then adding the result. Then, you will divide this by the total investment made to get the average return of the portfolio.

STEP 1: Enter the SUMPRODUCT function.

=SUMPRODUCT

STEP 2: Enter the first argument i.e. array1. Here, it is the range containing the amount invested (B2:B8).

=SUMPRODUCT(B2:B8

STEP 3: Enter the second argument i.e. array 2. Here, it is the range containing the return of each investment made (C2:C8).

=SUMPRODUCT(B2:B8,C2:C8)

Now we have the total return, we can easily get the average return (%) by dividing it by the total amount invested.

STEP 4: Enter the division operator (/) and then the SUM function.

=SUMPRODUCT(B2:B8,C2:C8)/SUM(

STEP 5: Select the range containing the amount invested i.e. B2:B8.

=SUMPRODUCT(B2:B8,C2:C8)/SUM(B2:B8)

Using the SUMPRODUCT and SUM functions, you can easily calculate weighted averages in any scenario.

 

Conclusion

SUMPRODUCT calculates the sum of products of corresponding elements within given arrays or ranges. This function finds application in various scenarios, including basic calculations like total revenue from sales, complex operations involving different arithmetic operators, and calculating weighted averages, such as determining the weighted average return on investments in a diverse portfolio.

Further Learning:

• Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria
• Advanced SUMPRODUCT Function: Count
• Advanced SUMPRODUCT Function: Conditional Date

Click here to know all about SUMPRODUCT in Excel!