How to Use Excel SUMPRODUCT Function?

Ads

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:

https://www.microsoft.com/en-in/microsoft-365/excel

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.

Syntax

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.

Remarks:
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.

Note:
1) Non-numeric entries will be treated as zeros.
2) VALUE! Error – Array 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.

Examples

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.

Then, we have to apply the above-mentioned formula to get the result, as shown in the following screenshot.

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.

Input that Causes #VALUE! Error

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

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 Aawexcel.com.