Excel Functions Tutorials
- Maths Functions in Excel
- ABS Function in Excel
- AGGREGATE Excel Function
- CEILING Excel Function
- COMBIN Excel Function
- COS Excel Function
- EVEN Function in Excel
- EXPONENTIAL Excel Function
- Excel Matrix Multiplication
- Excel Minus Formula
- FLOOR Function in Excel
- Histogram Formula
- INT Excel Function (Integer)
- LN Excel Function
- LOG Excel Function
- Mode in Excel
- ODD Function in Excel
- POWER Function in Excel
- PRODUCT Excel Function
- PRODUCT Formula in Excel
- PI in Excel
- Quotient in Excel
- Running Total in Excel
- RAND Excel Function
- Rand Excel Formula
- RANDBETWEEN in Excel
- MROUND in Excel
- ROUND in Excel
- ROUND Formula in Excel
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- SIGN Excel Function
- SIN Excel Function
- Square Root in Excel (SQRT)
- SUBTOTAL Excel Function
- SUM Function in Excel
- SUM Formula in Excel
- Excel SUM Shortcut
- SUMIF in Excel
- SUMIFS in Excel
- SUMIF Not Blank
- SUMIFS with Dates
- SUMIF Between Two Dates
- Sumif Text in Excel
- SumIf with Multiple Criteria
- SUMPRODUCT Function in Excel
- SUMPRODUCT Formula in Excel
- SUMPRODUCT with Multiple Criteria
- How to Sum Multiple Rows in Excel?
- TAN Excel Function
- TANH in Excel
- Average vs Weighted Average
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
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 –