The Sumproduct function can perform the entire calculation when you have two or more sets of values in the table form, and you need to determine the product or multiplications of those numbers and their sum. With the help of this function, you can obtain both the sum and product of the chosen array in a single cell. It gives back an array's or matching range's product sum. Now, let's get more details and see what is sumproduct in excel.

What Is the SUMPRODUCT Formula in Excel?

It is an Excel function that allows you to multiply ranges of cells and arrays and returns the product's sum. It multiplies the supplied array values before adding them. It is a Math or Trig Function. You can type the sumproduct function onto a worksheet cell as a formula component. It is a beneficial function that can be used in various ways depending on your needs. It can handle arrays in multiple ways and assist in comparing data in two or more ranges. Additionally, it aids in data calculations involving several criteria.

Syntax

For multiplication, which is the default function:

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

  • Array 1(required): this array consists of the elements that you first want to multiply and, after that, add them.
  • [array2], [array3],...(optional): these are array parameters with elements you want to multiply and add, ranging from 2 to 255.

Remark: Use SUMPRODUCT as usual, but substitute the desired arithmetic operators (*, /, +, -) for the commas separating the array parameters. Following completion of all operations, the findings are summed as usual.

How Does Sumproduct Work?

You can use sumproduct for essential arithmetic functions, and you can also use it for more advanced use; let's take a look. 

Basic Use

Calculating the sum of the products of comparable numbers in one or more ranges is the most typical and fundamental usage of sumproduct.

SUMPRODUCT_Excel_1

Explanation: the SUMPRODUCT performed this calculation: (2*250)+ (4*1000)+ (4*1000)+ (3*50)

Advanced Use

The SUMPRODUCT function is incredibly flexible and can achieve the same outcome as several Excel's built-in functions or even array formulae!

To understand it better, let's look at The COUNTIF function, which counts the number of cells with precisely one moon.

SUMPRODUCT_Excel_2

Now, if you use the sum product, you will find the same result

SUMPRODUCT_Excel_2_2.

Explanation: --(A1:A7="moon") reduces to the below-mentioned array constant:

--{TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}

The double negative -- drives these Booleans to 1's and 0's (TRUE=1, FALSE=0). That results in:

{1;0;0;0;1;1;0}

The SUMPRODUCT method receives this array constant as an argument and returns three as a result.

Properties of SUMPRODUCT

Now that you are aware of what is sumproduct in excel, it is time to take a closer look at its properties, which you must be mindful of before using the SUMPRODUCT function.

  • One array is the bare minimum that must be provided to the SUMPRODUCT function. If only one array is provided, the function returns the total of all the elements in this array.
  • If the array specified as parameters does not have the same amount of columns and rows, then the function returns the #VALUE! Error
  • The SUMPRODUCT function treats any non-numeric values contained in the arrays supplied as parameters as 0s.
  • The function accepts up to 30 arrays as parameters.
  • The non-numeric entries in an array are treated as zeroes by the SUMPRODUCT excel function.

Examples

1. Consider a scenario where you have information about the product inventories of a clothing store. The pricing of numerous goods, such as shirts, trousers, and t-shirts, and the required amount is provided. You must determine the typical price of a product at that store. A2:A6 represents the price, while B2:B6 represents the equivalent amounts. The formula to determine the necessary weighted average is:

=SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)

The resulting value will provide the overall average cost of all the items in that store.

SUMPRODUCT_Excel_3

2. Now, in this example, we will find out the sales in the west region. 

The formula used is: =SUMPRODUCT(--(B1:B6=" WEST"), C1:C6)

SUMPRODUCT_Excel_4.

To make Excel translate the TRUE and FALSE values into 1s and 0s, we included the double negative (- -).

Kick-start your career growth story with our PGP in Business Analysis. Get a chance to master Excel, Tableau, and Python tools. Start learning now!

Choose the Right Program

Unlock the power of data with Simplilearn's Business Analytics courses. Gain the skills to analyze, interpret, and make informed business decisions. From data visualization to predictive modeling, our comprehensive programs ensure you stay ahead in the dynamic world of analytics. Enroll today and transform your career!

Program Name

Post Graduate Program In Business Analysis

Business Analyst

Business Analytics For Strategic Decision Making

GeoAll GeosAll GeosIndia
UniversityPurdueSimplilearnIIT Roorkee
Course Duration6 Months11 Months6 Months
Coding Experience RequiredNoNoNo
Skills You Will Learn18+ skills including SQL, Tableau, PowerBI and more
Python, Jira, Excel, MySql and more
statistical analysis, data visualization, machine learning, neural networks, and much more.
Additional BenefitsEarn 35 PDs/CDUs Capstone from 3 domains and 14+ projects Purdue Alumni Association Membership 24x7 Learning support from mentors Capstone projects involving real world data setsAccess to Integrated Practical Labs Caltech CTME Circle Membership
Cost$$$$$$$$$$
Explore Program Explore Program Explore Program

Conclusion

In summarizing, you now understand the SUMPRODUCT function, its operation, its characteristics, and its adaptability. This function has various applications. Once you get how the function operates, it handles arrays in an easy-to-understand manner and is simple to use. Excel is the most used tool; thus, working knowledge of it is crucial if you want a successful profession. If you want to enhance your knowledge, then BA with Excel is the perfect course. It is helpful for both experienced and beginners. 

Our Learners Also Asked

1. What Is The Difference Between Sum And Sumproduct?

The SUMPRODUCT function in Excel adds up arrays and ranges and returns the products' total. The very flexible function SUMPRODUCT can also be used to sum and count similarly to SUMIFS or COUNTIFS. Whereas SUM adds values using addition. You can add specific values, cell references, specific ranges, or a combination of all three.

2. How Do You Use The Sumproduct Example?

For instance, the following worksheet has numerical values in columns A and B:

Column A

Cell A1, Cell A2, and Cell A3 contain 2, 3, and 4, respectively. 

Column B

Cell B1, Cell B2, and Cell B3 contain 2, 6, and ​1

​respectively. 

Use the formula =SUMPRODUCT(A1:A3, B1:B3) you will get the result 26. This formula's output is calculated as (2*2) + (3*6) + (4*1). SUMPRODUCT multiplies the corresponding values of the given arrays and adds the finished products.

3. Why Is Sumproduct Giving Value?

The #VALUE! error will appear if one or more of the specified range's cells include text or are represented as a Text data type. The content might be a computation from another formula, or perhaps the cell is just improperly structured.

4. How Do You Count Sumproduct In Excel?

To count, you can use the formula: =SUMPRODUCT(COUNTIF(range, things). You can utilize the COUNTIF function inside the SUMPRODUCT function to determine how many cells contain one of the various values.

5. What Can I Use Instead Of Sumproduct?

The functions SUM() and IF() can be combined to create a different formula. Remember that SUMPRODUCT can be used in place of SUM, and the formula will still function.

6. How Do I Combine SUMIF And Sumproduct?

SUMPRODUCT is more dependent on mathematical calculations. SUMIFS relies more on logic. SUMPRODUCT can be used to calculate conditional sums and the sum of products. The sum of products cannot be determined using SUMIFS. To find conditional sums, either function may be used, and both can handle many conditions.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Professional Certificate Program in Data Engineering

Cohort Starts: 16 Dec, 2024

7 months$ 3,850
Post Graduate Program in Data Analytics

Cohort Starts: 20 Dec, 2024

8 months$ 3,500
Professional Certificate in Data Analytics and Generative AI

Cohort Starts: 20 Dec, 2024

22 weeks$ 4,000
Caltech Post Graduate Program in Data Science

Cohort Starts: 23 Dec, 2024

11 months$ 4,000
Post Graduate Program in Data Science

Cohort Starts: 2 Jan, 2025

11 months$ 3,800
Data Scientist11 months$ 1,449
Data Analyst11 months$ 1,449

Get Free Certifications with free video courses

  • Introduction to Data Analytics Course

    Data Science & Business Analytics

    Introduction to Data Analytics Course

    3 hours4.6282K learners
  • Business Analysis Basics

    Business and Leadership

    Business Analysis Basics

    2 hours4.679K learners
prevNext

Learn from Industry Experts with free Masterclasses

  • Crack the Code to Data Analytics: Expert Tips for Non-Data Professionals

    Data Science & Business Analytics

    Crack the Code to Data Analytics: Expert Tips for Non-Data Professionals

    16th Dec, Monday9:30 PM IST
  • How Can You Master the Art of Data Analysis: Uncover the Path to Career Advancement

    Data Science & Business Analytics

    How Can You Master the Art of Data Analysis: Uncover the Path to Career Advancement

    4th Aug, Friday9:00 PM IST
  • Path to $100k+ Salary Career in Data Analytics: Build & Grow Your Career

    Data Science & Business Analytics

    Path to $100k+ Salary Career in Data Analytics: Build & Grow Your Career

    16th Oct, Wednesday6:30 PM IST
prevNext