SUMPRODUCT


Explanation of the SUMPRODUCT function

The SUMPRODUCT function performs 2 mathematical operations against the numbers that you provide as inputs:

Step 1 – Calculates the product of each respective element within your arrays
Step 2 – Sums up all of the products calculated in step 1

The image below shows the arguments that the function is asking for:

  1. At least 1 array
  2. Up to a total of 255 arrays (optional)

Note: The arrays that you input into the function need to be the same dimension.

SUMPRODUCT syntax in Microsoft Excel

Example 1 – Manual Calculation (2 columns) vs. SUMPRODUCT

  • On rows 5, 6, and 9 below I use the PRODUCT function
  • On rows 7 and 8 I use the mathematical operator for multiplication in Microsoft Excel which is the asterisk * symbol

SUMPRODUCT function in Microsoft Excel

Example 2 – Manual Calculation (3 columns) vs. SUMPRODUCT

  • On rows 5, 6, and 9 below I use the PRODUCT function
  • On rows 7 and 8 I use the mathematical operator for multiplication in Microsoft Excel which is the asterisk * symbol

SUMPRODUCT example in Excel

What happens if you input only 1 array?

The function will return the SUM of your array. It would be the same as if you used the SUM function.

Note: You need to pass each array as a separate array to the SUMPRODUCT function, or else you will end up with just the SUM of your data as shown in the image below. Notice that there are no commas in my SUMPRODUCT formula below which indicates that I inputted only 1 array.

SUMPRODUCT only 1 array

What happens if you input only 1 value?

The function will return the same value.

 

Go back to Excel Tutorials.