DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How to Use Excel SUMPRODUCT Function in Office 365?

#excelfunctions #excelsumproductfunct

Excel SUMPRODUCT Function:

Excel SUMPRODUCT Function
Excel SUMPRODUCT Function

In this tutorial, we will discuss the explanation, syntax, and usage of the Excel SUMPRODUCT Function in Office 365. Get an official version of Microsoft Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel.

Explanation of SUMPRODUCT Function:

  • The SUMPRODUCT function multiplies the corresponding arrays or ranges and returns the sum of the products.
  • In a simple sentence, the values within the arrays are multiplied and the products are added and returned as output.
  • The default operation of this function is multiplication, however, it can perform addition, subtraction, and division.

SUMPRODUCT Function – Basic Syntax:

The fundamental syntax of SUMPRODUCT Function is,

=SUMPRODUCT(array1, [array2], [array3], ...)
Enter fullscreen mode Exit fullscreen mode

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.

Remarks:

  • The function will simply sum the values in the array if only one array is supplied.
  • 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, pressing _ ‘Enter’ _ would be enough.
  • To do other arithmetic operations,
    • Use the arithmetic operators **(*, /, +, -) **by replacing the commas that are used to separate the array arguments. For example,
    • As usual, after all the operations are done, the products will be summed and returned as output.
    • Enclose your array arguments within the parentheses while using arithmetic operators so it controls the order of operators.

Important Note:

  • *Non-numeric entries will be treated as zeros. *
  • #VALUE! Error – Array arguments must have the same size/ranges or else the function returns the #VALUE! Error. For example, =SUMPRODUCT(A1:A5, C1:C6) will return an error.
  • 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.

Practical Examples:

Let’s look at some practical examples of SUMPRODUCT Function and explore how to use it in Microsoft Excel.

Example 1: Two arrays are multiplied and the products are added

  • In this example, we are going to multiply the Quantity and Price of the products, and the output will return as a sum.
  • First, we have to select the 1st array range from E4:E6 and the 2nd array range from F4:F6.
  • Then, refer to the below syntax of the SUMPRODUCT function to return the output. 
=SUMPRODUCT(E4:E6,F4:F6)
Enter fullscreen mode Exit fullscreen mode

Example 1
Example 1

  • The SUMPRODUCT calculates these arrays as follows: 
=(10\*10000) + (22\*1500) + (57\*450)
Enter fullscreen mode Exit fullscreen mode
  • Now, you have to press Enter so the output will return as the product’s sum in the F8 cell.

Example 1 - Output
Example 1 – Output

Example 2: Non-numeric Entries treated as Zeros

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

Non-numeric entries treated as Zeros
Non-numeric entries treated as Zeros

Example 3: #VALUE! Error

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

#Value! Error
#Value! Error

A Brief Summary:

In this short tutorial, we have illustrated how to use EXCEL SUMPRODUCT Function in Office 365 with clear-cut practical examples. If you have any queries/doubts , kindly share it in the below comment section. Thanks for visiting Geek Excel!! Keep Learning With Us!!

Read Ahead:

Top comments (0)