What is the SUMPRODUCT in Excel and how to use it?

SUMPRODUCT is a built-in function in Microsoft Excel that multiplies the range of cells or arrays and returns the sum of products. To calculate the sum of the products of corresponding numbers in one or more ranges, you can use Excel’s powerful SUMPRODUCT function. SUMPRODUCT first multiplies then adds the values of the cells or arrays. It can be entered as a part of a formula in a cell of a worksheet. It is a brilliant function that can be used in many ways depending on the requirement. The default operation is multiplication, but addition, subtraction, and division are also possible using the SUMPRODUCT function.

Basic usage of SUMPRODUCT in Microsoft Excel

Step 1. To understand the Excel SUMPRODUCT function how it works, consider the following example:-

Suppose some fruits and their weights are mentioned in cells B2:B11 and prices in cells C2:C11, and you wish to find out the total value of fruits. To find out the total value of the fruits, you will need to multiply the cells B2 to cell C2, and so on till cell B11 to cell C11; further, you will add all these values to find out the total price of fruits. In other words, first, you will find out every fruit price separately and then add their value to find out the total price of the total fruits. With the help of Microsoft Excel’s SUMPRODUCT formula, you can get your desired result in one go. You can use the formula:-

=SUMPRODUCT(B2:B11,C2:C11)

The SUMPRODUCT function performs this calculation: (1 * 133) + (4 * 12) + …. + (2 * 34) = 31575.

Step 2. While using the SUMPRODUCT function, ensure the ranges have the same dimension; otherwise, Excel will display the #VALUE! Error.

Step 3. The SUMPRODUCT function treats any cell that is not numeric or blank as zero.

Step 4. If you supply a single cell range, the SUMPRODUCT function produces the same result as the SUM function.

For best performance, SUMPRODUCT should not be used with full-column references. Consider =SUMPRODUCT(B:B,C:C), where the function will multiply the 1,048,576 cells in column B by the 1,048,576 cells in column C before adding them.

With the above examples, you can easily learn the basic function of the SUMPRODUCT formula. For advanced usage of the SUMPRODUCT function, refer to Microsoft guidelines or ask an expert in the Excel Tech Community.

Leave a Reply