EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Maths Function in Excel SUMPRODUCT Formula in Excel
 

SUMPRODUCT Formula in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated May 8, 2023

Feature Image

 

 

SUMPRODUCT Formula in Excel

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

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 and array2 are the cell ranges or an array whose cells we want to multiply and add. If 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. While in previous versions of Excel, we had 30 arrays. We can have a maximum of 255 arrays in a newer version of Excel.

Suppose we have two arrays and 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 and 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 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 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 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, sales 1 for the 1st quarter and sales 2 for the 2nd quarter, and we wish to find out the number of products sold less in sales 1 compared to sales 2.

Below is the sample sales data.

Sample Sales Data

We will now write the formula 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 per zone, and we want to know the 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, we use a double negative mark to convert logical values (True and false) into numeric values (0, 1). We can do the same with 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 using the SUMPRODUCT function in multiple criteria.

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

Region-Wise Sales Data

Suppose we want to count the sales of JAWA bikes in the North region. We can do so by writing the Excel SUMPRODUCT formula 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, we now want to know the total sales of JAWA bikes 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 is 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 of arguments should have the same size. If they are not 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 prior versions of Excel, you could only provide up to 30 arrays as arguments.
  • If you have one array or have not provided the second array, the function will return the sum of that single array.
  • If the arguments in the 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.
  • This function does not support wildcard characters like “*” and “?”.
  • 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 a 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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download SUMPRODUCT Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download SUMPRODUCT Formula Excel Template

EDUCBA

डाउनलोड SUMPRODUCT Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW