HomeExcel Functions ► Excel SUMPRODUCT FunctionSyntax For Excel SumProduct Function

What is the use of Excel Sumproduct Function?

Excel Sumproduct Function can help us to multiple & sum specified ranges or arrays in excel. Is simple words, It will first multiply the corresponding cells from ranges & then sum up all the values.

 Definition for Excel SumProduct Function

In above example, I have used Sumproduct to multiple & sum up the values from column D & column F. First it has multiplied the values from column D with column F & then sum up the values. By using excel Sumproduct function you can perform a complex calculation in a single cell.

Syntax for Excel Sumproduct Function

SUMPRODUCT(array1, [array2], [array3], …)

array1 First array you want to multiply & then add. [Required]

array2 Second array you want to multiply & then add. [Optional]

Additional Information on Excel Sumproduct Function

  • If skip you to specify array2, Sumproduct will simply sum the array1.
  • The maximum size of each array should be same. If array1 has 5 cells then cells in array2 should be 5.
  • Text & other non-numeric entries will be treated as 0.

Examples for Excel Sumproduct Function

Examples For Excel Sumproduct Function
V = Vendor W =Week

In above example, I have used Excel Sumproduct to get a value on some conditions. As I have already mentioned that if you do not specify array2, it will simply sum array 1. And, here I have used the same logic to get the value from a condition. Let me explain.

Examples To Understand Excel SumProduct function

  • First, it will check which vendor you have selected.
  • Then it will check which week you have selected.
  • And, return the value from the cell intersect on the both.

The array will work some this like this when you select V-3 & W-2

How Excel SumProduct Function Works With Arrays

Download Sample File

Download Sample File To Understand Excel Sumprodunct Function

Tips Related To Excel SumProdunct Function

Conclusion

Excel Sumproduct Function in one of the most useful Excel functions. You can different way once you get into it. Calculating Weighted Average is one of the most common uses of this function. Learn more about SumProduct from here.

Related Tips

Sum | Sumif | Sumifs

Blog | Excel Functions