Pinterest Pixel

3 Examples to Master SUMPRODUCT in Excel

You might be familiar with the essential function like SUM and PRODUCT in Excel, but there is... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

3 Examples to Master SUMPRODUCT in Excel | MyExcelOnline 3 Examples to Master SUMPRODUCT in Excel | MyExcelOnline

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.

See also  CountA Formula in Excel

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

3 Examples to Master SUMPRODUCT in Excel

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

3 Examples to Master SUMPRODUCT in Excel

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)

3 Examples to Master SUMPRODUCT in Excel

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

3 Examples to Master SUMPRODUCT in Excel

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 –

See also  The Ultimate Guide to Excel CELL Function

=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

3 Examples to Master SUMPRODUCT in Excel

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

=SUMPRODUCT(B2:B9

3 Examples to Master SUMPRODUCT in Excel

STEP 3: Enter the division operator (/).

=SUMPRODUCT(B2:B9/

3 Examples to Master SUMPRODUCT in Excel

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)

3 Examples to Master SUMPRODUCT in Excel

=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

3 Examples to Master SUMPRODUCT in Excel

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.

See also  Advanced Excel SUMPRODUCT Function: Sum Multiple Criteria

3 Examples to Master SUMPRODUCT in Excel

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

3 Examples to Master SUMPRODUCT in Excel

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

=SUMPRODUCT(B2:B8

3 Examples to Master SUMPRODUCT in Excel

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)

3 Examples to Master SUMPRODUCT in Excel

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(

3 Examples to Master SUMPRODUCT in Excel

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

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

3 Examples to Master SUMPRODUCT in Excel

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

3 Examples to Master SUMPRODUCT in Excel

 

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.

See also  RANDARRAY Formula in Excel

Further Learning:

Click here to know all about SUMPRODUCT in Excel!

If you like this Excel tip, please share it
3 Examples to Master SUMPRODUCT in Excel | MyExcelOnline 3 Examples to Master SUMPRODUCT in Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...