Sumproduct formula in excel is an array formula which is used to calculate the sum after we calculate the product of arrays provided, this formula has two steps as said above first to calculate the product and then calculate the sum of all the products, thus the name given as SUMPRODUCT, as this is an array formula so to calculate it we need to press CTRL + SHIFT + ENTER.
Sumproduct Formula in Excel – Table of Contents
Excel SUMPRODUCT Formula
The Formula of SUMPRODUCT function in Excel.
- Array 1: This is the first range that we required to multiply or add.
- Array 2: This is the second range that we required to multiply with array 1. Finally adds together the result.
When you want to calculate the sales price of each product with their units sold and unit price and finally you want to total the total sale value, you need to go through a lengthy process.
For an example look at the below data.
To calculate the sale value Units price * Unit Price.
Now the total sale value is 20 + 12 + 2 = 34.
But SUMPRODUCT function can do this in simple one step.
#1 – How to Use SUMPRODUCT Formula in Excel?
Below are the examples of SUMPRODUCT Formula in excel.
SUMPRODUCT Formula Excel Example #1
SUMPRODUCT not only multiples and add it also adds together all the values just like our SUM function. For an example look at the below image.
Apply the SUMPRODUCT Formula in cell C2.
So, it has returned the result of 115 just like our regular SUM function does.
SUMPRODUCT Formula Excel Example #2
Assume you have the product name, product units sold, and unit price in an excel sheet just like the below.
With normal calculation, we first multiply Units with Unit Price.
The Output is shown below:
Drag the Formula to other cells.
Now we will add together all the above values together.
We got the Total sales value as 534.
In order to eliminate this lengthy process of multiplying and adding together all the values, Microsoft has introduced SUMPRODUCT function.
Step 1: Open SUMPRODUCT function and Select the first array as B2 to B7.
Step 2: Now select the second array as C2 to C7.
Step 3: Close the bracket and hit the enter key.
So, SUMPRODUCT has returned the value of our other lengthy formula does. Both the results are the same.
#2 – Use SUMPRODUCT as Logical Function
Following are the examples to use Sumproduct as Logical function in excel.
SUMPRODUCT Logical Formula Excel Example #1
I hope you are aware of SUMIF function in excel. SUMIF performs the process of SUM based on the criteria given by the user.
For example, look at the below image example of SUMIF function.
The result is shown below:
On a similar note we can also use SUMPRODUCT as the logical test function and based on the TRUE or FALSE result we can add the values.
Step 1: Open SUMPRODUCT formula. Before selecting, any array type put two negative signs (–) first.
Step 2: Open new bracket and select the Array 1 as A2 to A13.
Step 4: Put equal sign and select the cell D3 to test all the values equal to North.
Step 5: Close the bracket and select Sales Column from B2 to B13.
Step 6: Close the bracket and hit the enter key. We will have total sales value for the region North.
Note: The reason we have added two negative signs because it will convert the first logical test from TRUE or FALSE to 1 or 0.
SUMPRODUCT Logical Formula Excel Example #2
SUMPRODUCT can also serve as a multiple condition test value. Take below data for an example.
From the above table if you want sum sales for the region North and for the product A we can use below formula.
Step 1: Open SUMPRODUCT Excel formula and put two negative signs.
Step 2: Select the array 1 as A2 to A13.
Step 3: Put equal sign and test whether this is equal to the North or not.
Step 4: Now enter two more negative signs and select the second array 2 as B2 to B13.
Step 5: Put an equal sign and select F3 cell.
Step 6: Select array 3 as sales column i.e. C2 to C13.
Step 7: Close bracket and hit enter key. We will have total sales value for the North region and for Product A.
Like this, we can use SUMPRODUCT as a logical function in Excel as well.
Things to Remember About SUMPRODUCT Formula in Excel
- When the two negative signs (–) supplied I will convert the logical TRUE or FALSE values to 1 or 0.
- SUMPRODUCT first multiplies then add values together.
- Multiplication will be made of the corresponding row.
- All the arrays length should be the same.
- If the array length is not the same length then we will get a #VALUE error.
- We can use this as an alternative to SUMIF & SUMIFS function in excel to sum based on criteria’s.
This has been a guide to SUMPRODUCT Formula in Excel. Here we discuss how to use SUMPRODUCT Excel Formula along with examples and downloadable excel template. You may learn more about excel from the following articles –