WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » SUMPRODUCT Excel Function

SUMPRODUCT Excel Function

By Tanuj KumarTanuj Kumar | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Tanuj Kumar

SUMPRODUCT in Excel

The SUMPRODUCT function has categorized as Math or Trig function in excel used to multiple the array or range and return the sum of a product.  It is used to calculate a weighted average. SUMPRODUCT formula is used to calculate the sum of the products of corresponding numbers in one or more ranges/arrays. In other words, the SUMPRODUCT function multiplies the corresponding components in the given arrays and returns the sum of those products.

You can extend the functionality of SUMPRODUCT Formula by using other functions inside it.

Syntax

SUMPRODUCT Formula

SUMPRODUCT Formula has one array as compulsory parameter and n numbers of arrays as optional parameters i.e., arrary1 and [array2]…

Compulsory Parameter:

  • array1: This parameter is the first array or range that will be multiplied then added.

Optional Parameter:

  • [array2]: The second array or ranges that will be multiplied then added. It is optional
  • [array3]: The third and onwards arrays or ranges that will be multiplied then added.

Examples to use SUMPRODUCT Function in Excel

You can download this SUMPRODUCT Function Excel Template here – SUMPRODUCT Function Excel Template

Example #1

Let’s consider a set of products from A to J, and their price and units listed in the subsequent columns now calculate the sum of all the listed products by SUMPRODUCT formula. And the o/p of SUMPRODUCT function i.e. =SUMPRODUCT($B$5:$B$14,$C$5:$C$14) = 20,0600 as shown in the below table.

SUMPRODUCT in Excel Example 1

Example #2

In this example, we take a month and region-wise sale data and determine the total sales for the west region by applying the SUMPRODUCT Formula as follows:

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

=SUMPRODUCT(–($C$20:$C$31=”West”),$D$20:$D$31) here – – is used to convert true false array into 0’s and 1’s.

The output will be 1,500, as shown in the below table.

SUMPRODUCT in Excel Example 2

Example #3

We can use the SUMPRODUCT function to calculate a weighted average where each product has assigned a weight.  Suppose we are given the following data.

SUMPRODUCT in Excel Example 3

The output will be: 55.80%=SUMPRODUCT(C36:C46,D36:D46)/SUM(D36:D46)

Example #4

The SUMPRODUCT function can be used with multiple criteria. Consider the given set of data, as shown in the below table. Now calculate the total January sales for the west region. Then apply the Excel SUMPRODUCT formula- =SUMPRODUCT((D53:D64*E53:E64)*(B53:B64=”Day shift”)*(C53:C64=”West”))

And the output will be – 10.

Example 4

The SUMPRODUCT function in Excel can be used as a VBA function.

Sub ABC()
MsgBox Evaluate(“=SUMPRODUCT(($A$1:$A$5=””Tanuj””)*($B$1:$B$5))”)
End Sub

Things to Remember

  • SUMPRODUCT formula is used to calculate a weighted average.
  • SUMPRODUCT through the #VALUE error when the arrays provided have different dimensions/range in the array.
  • This function can accept non-numeric entries that are used in the array and consider them as zeroes.
  • SUMPRODUCT accepts up to 255 parameters in the latest versions of Excel above 2007 and 30 in earlier Excel versions.
  • Logical tests inside arrays will create TRUE and FALSE values. In most cases, it is advisable to convert them to 1’s and 0’s.
  • SUMPRODUCT and MONTH can be used to get a sum of values for a given month.

SUMPRODUCT Excel Function Video

Recommended Articles

This has been a guide to SUMPRODUCT Function in Excel. Here we discuss the SUMPRODUCT Formula and how to use it along with an excel example and a downloadable template. You may also look at these useful functions in excel –

  • SUMPRODUCT with Multiple Criteria
  • SUBTOTAL Excel Function
  • AGGREGATE Function
  • SIGN Excel Function
  • Redo Shortcut in Excel
10 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel Tutorials,  Excel, VBA & Power BI

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download SUMPRODUCT Function Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More