EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel SUMPRODUCT Formula in Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

SUMPRODUCT Formula in Excel

By Madhuri ThakurMadhuri Thakur

Feature Image

Excel SUMPRODUCT Formula (Table of Contents)

  • SUMPRODUCT Formula in Excel
  • How to Use SUMPRODUCT Formula in Excel?

SUMPRODUCT Formula in Excel

SUMPRODUCT formula in Excel is a function that does both multiplication and sum of cells simultaneously. SUMPRODUCT formula first multiplies the cell range/arrays and returns the sum of products in excel.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,898 ratings)

This function can be used as a workbook function. SUMPRODUCT can be found under Math and Trigonometry formulas in excel. This function is present in all versions of Excel. This function is very commonly used in Excel. It can also be used in multiple ways, which are described in the below examples as well. This function is helpful in financial analysis as well. It compares data in several arrays and also calculates data with different criteria.

Below is the formula or Syntax of SUMPRODUCT in Excel :

Syntax

In the above syntax, array1, array2 are the cell ranges or an array whose cells we want to multiply and then add. In case we only have one array, the function will only sum the cell range or array. So for the function to give the sum of products, we should have 2 arrays. In a newer version of Excel, we can have a maximum of 255 arrays. While in previous versions of Excel, we had 30 arrays.

Suppose we have two arrays, and we want to know the sum of their products.

=SUMPRODUCT({2,4,5,6},{3,7,8,9})

= (2*3)+(4*7)+(5*8)+(6*9)

= 6+28+40+54

= 128

This is how first the elements of the 1st array are multiplied with the first element of a 2nd array and so on. And then their product is added.

How to Use SUMPRODUCT Formula in Excel?

SUMPRODUCT Formula in Excel is very simple, easy to use. Let us now see how to use the SUMPRODUCT Formula in Excel with the help of some examples. These examples will surely help you with a clear understanding of the function.

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

Excel SUMPRODUCT Formula – Example #1

In our first example, we will first calculate the SUMPRODUCT of two cell ranges. Below is the sample data.

Cell Ranges

Now we will calculate the SUMPRODUCT of these arrays. We will write the formula as below:

Calculating Arrays

Press Enter to see the result.

Result

Here is the explanation of the above formula:

=(12*16)+(10*22)+(8*14)+(20*30)+(12*8)+(4*41)

= 192+220+112+600+96+164

=1384

Excel SUMPRODUCT Formula – Example #2

Here we have data for Electrical products. We will now calculate their SUMPRODUCT.

SUMPRODUCT Formula Example 2-1

Now, we will write the formula:

Formula

Press Enter to see the result.

SUMPRODUCT Formula Example 2-3

In the above formula, the first cell of the first array (Qt.) is multiplied by the second array’s first cell (Price). The second cell of the first array will be multiplied by the second cell of the second array and so on until the arrays’ fifth cell. After the arrays are multiplied, their product is then added up to give the SUMPRODUCT, which is 799.

In case we have only one array, as shown below and we write the formula:

Using the Formula

Press Enter to see the result.

SUMPRODUCT Formula Example 2-5

The function will now give the result of a SUM function. That is the sum of all cells in the range.

Excel SUMPRODUCT Formula – Example #3

In this example, we will see how the function works with single or multiple criteria.

Suppose we have products in column A, and sales 1 for 1st quarter and sales 2 for the 2nd quarter and we wish to find out the number of products that are sold less in sales 1 compared to sales 2.

Below is the sample sales data.

Sample Sales Data

We will now write the formula as below:

SUMPRODUCT Formula Example 3-2

Press Enter, and we will see the below result.

SUMPRODUCT Formula Example 3-3

Here 4 products in sales 1 are sold less compared to sales 2. The double-negative (–) in the formula is used to convert True and False into 1 and 0 in Excel.

Also, there is another way of doing this.

SUMPRODUCT Formula Example 3-4

Press Enter, and you will get the same result 4.

SUMPRODUCT Formula Example 3-5

Excel SUMPRODUCT Formula – Example #4

Here we have another example in which we have sales data as per zones, and we want to know the sum of sales for a particular zone, say West zone.

Sales for Particular Zone

Now we will write the formula in cell B12.

SUMPRODUCT Formula Example 4-2

Press Enter to see the result.

SUMPRODUCT Formula Example 4-3

As mentioned above, to convert logical values (True and false) into numeric values (0, 1), we use a double negative mark. We can do the same by a “*” sign.

We can also write the formula as shown below using “*” instead of “- -”

SUMPRODUCT Formula Example 4-4

Press Enter to see the result.

SUMPRODUCT Formula Example 4-5

Excel SUMPRODUCT Formula – Example #5

We will now look into the use of the SUMPRODUCT function in multiple criteria.

Let’s say we have region-wise sales data of bikes.

Region-Wise Sales Data

Suppose if we want to count the sales of JAWA bikes in the North region. We can do so by writing the excel SUMPRODUCT formula as below:

SUMPRODUCT Formula Example 5-2

Press Enter to know the count of JAWA bikes sold in North.

SUMPRODUCT Formula Example 5-3

Another way of writing the above formula is shown below:

It will also give the same result. Here we have used the * sign instead of the – sign to convert True and False into 0 and 1.

SUMPRODUCT Formula Example 5-4

Press Enter to see the result.

SUMPRODUCT Formula Example 5-5

Taking the above scenario, now we want to know the total sales of JAWA bike in the North region.

For this, we will write the SUMPRODUCT formula in excel as below:

SUMPRODUCT Formula Example 5-6

Press Enter to know the total sales.

SUMPRODUCT Formula Example 5-7

Also, we can write the above formula as below:

SUMPRODUCT Formula Example 5-8

Press Enter to see the results.

SUMPRODUCT Formula Example 5-9

An asterisk (*) sign here acts as an OR operator in the above example.

Excel SUMPRODUCT Formula – Example #6

A SUMPDOUCT formula is also used to calculate the weighted average in Excel. The formula to calculate the weighted average is =SUMPRODUCT(value, weight)/SUM(weights)

Let’s understand this better with this example:

SUMPRODUCT Formula Example 6-1

Here we have 5 different projects, their completion status and their importance (rankings). We will now write the formula for a weighted average using the SUMPRODUCT function.

SUMPRODUCT Formula Example 6-2

Press Enter to see the result.

SUMPRODUCT Formula Example 6-3

Here is how we calculate the weighted average.

Going through all the above examples will give you a better understanding of the function and its implications in different scenarios.

Things to Remember

  • The array provided as arguments must be of the same size. If they are not of the same size or dimensions, you will get #VALUE! Error.
  • The arrays should not contain text or non-numeric values. Because then all such cells will be treated as 0’s.
  • In the older versions of Excel, only up to 30 arrays can be provided as parameters.
  • If you have one array or have not provided the second array, then the function will return the sum of that single array.
  • If the arguments in function are logical (True and False), they must be converted into numeric values (0, 1). This can be done by adding — sign as shown in the above examples.
  • Wildcard characters like “*” and “?” are not supported by this function.
  • SUMPRODUCT Formula can even give results from a closed workbook in excel.

Recommended Articles

This has been a guide to SUMPRODUCT Formula in Excel. Here we discuss how to use SUMPRODUCT Formula in Excel along with excel examples and downloadable excel template. You can also go through our other suggested articles –

  1. SUMPRODUCT Function in Excel
  2. SUM Function in Excel
  3. SUMIF Formula in
  4. PRODUCT Function in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download SUMPRODUCT Formula Excel Template

EDUCBA

Download SUMPRODUCT Formula Excel Template

EDUCBA

डाउनलोड SUMPRODUCT Formula Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more