Skip to content

How to Use Excel SUMPRODUCT Function?


Excel SUMPRODUCT Function: Microsoft Excel is a powerful data visualization and analysis program used all around the world. It uses spreadsheets to store, organize, or perform operations with formulas and functions. If you want to multiply the given ranges and find the sum of the products, then we suggest you use the Excel SUMPRODUCT function.

This tutorial will discuss the explanation, syntax, and usage of the Excel SUMPRODUCT Function. Get an official version of Microsoft Excel from the following link:

Excel SUMPRODUCT FunctionPin
Excel SUMPRODUCT Function

Explanation of SUMPRODUCT Function

  • It is one of the built-in functions in Microsoft Excel.
  • The SUMPRODUCT function multiplies the corresponding arrays or ranges and returns the sum of the products.
  • The values within the arrays are multiplied, and the products are added and produced as output.
  • The default operation of this function is multiplication. However, it can perform addition, subtraction, and division.


  • Here, you will see the syntax of the SUMPRODUCT function.
  • To apply this function on your spreadsheet, you have to select a cell and enter the formula in the following format.
  • Once you enter the formula, just click on the Enter button to get the result.

=SUMPRODUCT(array1, [array2], …)

Argument Description:

  • Array1 (Required) – The ranges of arrays/cells that you want to multiply and then add.
  • [array2], [array3],…(Optional) – Array arguments 2 to 255 whose values you wish to multiply and then sum. You can give up to 30 arrays.

1) The function will sum the values in the array if only one array is supplied.
2) Although this function deals with arrays, you don’t have to follow the normal array syntax. That means you don’t have to press Ctrl + Shift + Enter after entering the ranges. Like a non-array formula, pushing ‘Enter’ would be enough.
3) To do other arithmetic operations, Use the arithmetic operators (*, /, +, ) by replacing the commas used to separate the array arguments.
4) After all the operations are done, the products will be summed and returned as output.
5) Enclose your array arguments within the parentheses while using arithmetic operators, so it controls the order of operators.

1) Non-numeric entries will be treated as zeros.
2) VALUE! ErrorArray arguments must have the same size/ranges, or the function returns the #VALUE! Error. For example, =SUMPRODUCT(A1:A5, C1:C6) will return an error.
3) Don’t use full column references, for example, =SUMPRODUCT(A:A, C:C) as it will multiply 1,048,576 cells in column A with the 1,048,576 cells in column C and then add.


Let’s look at some practical examples of the SUMPRODUCT Function and explore using it in Microsoft Excel.

Two arrays are multiplied, and the products are added 

  • In this example, we will multiply the Quantity and Price of the products, and the output will return as a sum.
  • First, we must select the 1st array range from D3:D5 and the 2nd array range from E3:E5.
Input RangePin
Input Range
  • Then, we have to apply the above-mentioned formula to get the result, as shown in the following screenshot.
Enter the formulaPin
Enter the formula
  • Finally, we need to click on the Enter button to get the result.

Non-numeric Entries are treated as Zeros

  • If there are non-numeric entries within the array range, this function will consider them as zeros.
Ignore textPin
Ignore text

Input that Causes #VALUE! Error

  • The function returns the #VALUE! Error if the array arguments are not in the same size or range.
Value ErrorPin
Value Error

A Brief Summary

This short tutorial has illustrated how to use EXCEL SUMPRODUCT Function with clear-cut practical examples. If you have any queries/doubts, kindly share them in the below comment section. To learn more about Excel functions, then visit our webpage

Video Tutorial

The following video will show you how to apply the Excel SUMPRODUCT function in the spreadsheet.

Read Ahead:


Leave a Reply

Your email address will not be published. Required fields are marked *

Share to...