offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

What Is the Sumproduct Formula in Excel (and When Should You Use It?)

SUMPRODUCT is a function in Excel that multiplies range of cells or arrays and returns the sum of products. It first multiplies then adds the values of the input arrays. It is a ‘Math/Trig Function’. It can be entered as a part of a formula in a cell of a worksheet. It is a very resourceful function which can be used in many ways depending on the requirement of the user.

Syntax

The syntax of the SUMPRODUCT function is –

=SUMPRODUCT(array1, [array2,….. array_n])

array1 – this parameter is the first array or range that will be multiplied then added
array2….array_n – these parameters are the second and onwards arrays or ranges that will be multiplied then added. These are optional parameters.

How does SUMPRODUCT work

Let us look at a very basic example to try and understand how the SUMPRODUCT function works. Suppose we have 2 arrays – {3,5;6,1} and {4,2;7,8}. If we use SUMPRODUCT function on these two arrays, the formula will look like –

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

The output upon entering this formula in a cell will be 72. Let us understand the maths behind this result. The SUMPRODUCT function calculates the product as follows –

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

= 12+10+42+8

=72

As we can see, the SUMPRODUCT function multiplies and then adds the input arrays. The input arrays can also be written in terms of cell reference. So, if the array {3,5;6,1} is put in cells A3:B4 and {4,2;7,8} is put in D4:E5, then the function will look like –

=SUMPRODUCT(A3:B4, D4:E5)

When we put in this formula in a cell, the result will be the same. The return value of the function will be 72 in this case as well.

Properties of SUMPRODUCT

Having understood the basic working and use of SUMPRODUCT function, let us have a look at some properties of the function. Here are some properties of the SUMPRODUCT function that should be known before using the function:

  • If all the arrays provided as parameters do not have the same number of rows and columns, then the function will return the #VALUE! Error.
  • If the arrays provided as parameters have non-numeric values in them, then these values are treated as 0’s by the SUMPRODUCT function.
  • If only one array is provided, SUMPRODUCT will simply sum the items in the array.
  • Up to 30 arrays can be provided as parameters in the function.

Some Examples of SUMPRODUCT function

Let us look at a few more examples to understand the SUMPRODUCT function better as well as look at the versatility of the function.

Suppose we have a data of cells which contains data in the text format in cells B4:B21 and we want to find out the total number of characters in the data. Then we can use the SUMPRODUCT function to compute the required value. The formula to do so will be –

=SUMPRODUCT(LEN(B4:B21))

This formula has another function (LEN) integrated with the SUMPRODUCT function. The formula first calculates the length of all the characters in the data cell wise and then adds them all.

Let us look at another example. Imagine a case where you have a data of inventory of products at a garment shop. The data of the cost of various products such as shirts, jeans, t-shirts etc and their corresponding quantity is available. You have to calculate the average cost of a product in that shop. The price is in A2:A16 and corresponding quantities are in B2:B16. The formula to calculate the required weighted average will be

=SUMPRODUCT(A2:A16,B2:B16)/SUM(B2:B16)

The resultant value will give the average cost of all the products combined in that shop.

Another way to use SUMPRODUCT is count. To understand this better, let us have a look at another example. Suppose we have the data for the number of subscribers for various outlets in a state and we want to find out the total number of subscribers for a city. We can use the SUMPRODUCT function to calculate the number of subscribers for a city.

Let’s say we have data on retail stores in Maharashtra in A2:A16 and the corresponding number of subscribers registered at the retail store in B2:B16. To compute the total number of subscribers in a city, say Mumbai, we will use the formula –

=SUMPRODUCT(–(A2:A16=”Mumbai”, B2:B16))

This formula will give the total number of subscribers in Mumbai. Note that this formula uses the conditional operator “=”. The subscribers of retails in Mumbai only get added up as a result of this conditional operator.

This formula works on the principle of TRUE and FALSE. All the retails in Mumbai become TRUE and all other retails become FALSE under this condition. Then the B2:B16 argument simply calculates the sum of values marked as TRUE, which in this case is for the city Mumbai. Ultimately, we get the total number of subscribers in Mumbai from our data.

sumproduct excel

Conclusion

We have been introduced to the SUMPRODUCT function in this article. Then we looked at how this function works as well as its versatility. This function can be used in a large number of cases. It handles arrays in a simple manner and is easy to use once you understand how the function works.