What Is SUMPRODUCT In Excel? When Should You Use This Function?

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.

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.

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

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.

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)

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
Geo	All Geos	All Geos	India
University	Purdue	Simplilearn	IIT Roorkee
Course Duration	6 Months	11 Months	6 Months
Coding Experience Required	No	No	No
Skills You Will Learn	18+ 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 Benefits	Earn 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 sets	Access 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.