How to use SUMPRODUCT Function in Excel

SUMPRODUCT is one of the most powerful Excel functions. It can sum and multiply the ranges or arrays.

In simple words, it first multiplies the corresponding cells from ranges and then sum up all the values.


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

  1. array1 the first array you want to multiply and then add.
  2. [array2] the second array you want to multiply and then add.


In the below example, we have used SUMPRODUCT to multiple and sum up the values from column D and column F.

definition for excel sumproduct function

First, it has multiplied the values from column D with column F and then sums up the values.

Sample File


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