Excel SUMPRODUCT Formula (Table of Contents)
SUMPRODUCT Formula in Excel
SUMPRODUCT formula in Excel is a function that does both multiplication and sum of cells at the same time. SUMPRODUCT formula first multiplies the cell range/arrays and returns the sum of products in excel.
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 version 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 :
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 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.
Excel SUMPRODUCT Formula – Example #1
In our first example, we will first calculate the SUMPRODUCT of two cell ranges. Below is the sample data.
Now we will calculate the SUMPRODUCT of these arrays. We will write the formula as below:
Press Enter, to see the 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.
Now, we will write the formula:
Press Enter, to see the result.
In the above formula, the first cell of the first array (Qt.), is multiplied by the first cell of second array (Price). The second cell of the first array will be multiplied by the second cell of the second array and so on, till the fifth cell of both the arrays. 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:
Press Enter, to see the result.
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.
We will now write the formula as below:
Press Enter and we will see the below result.
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.
Press Enter and you will get the same result 4.
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.
Now we will write the formula in cell B12.
Press Enter, to see the result.
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 “- -”
Press Enter, to see the result.
Excel SUMPRODUCT Formula – Example #5
We will now look into the use of SUMPRODUCT function in multiple criteria.
Let’s say we have a region-wise sales data of bikes.
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:
Press Enter, to know the count of JAWA bikes sold in North.
Another way of writing the above formula is shown below:
It will also give the same result. Here we have used * sign instead of – sign to convert True and False into 0 and 1.
Press Enter, to see the result.
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:
Press Enter to know the total sales.
Also, we can write the above formula as below:
Press Enter to see the results.
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:
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.
Press Enter, to see the result.
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.
- In case you have one array or you 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 have to 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 –
