How to use the SUMPRODUCT function in Excel with an example

How to use the SUMPRODUCT function in Excel with an example. SUMPRODUCT is a function that returns the sum of the product of corresponding ranges or arrays. Let’s learn how to use the SUMPRODUCT function in Excel with an easy-to-understand example!

Assume that there is a dataset consisting of different numbers. The goal of the user is to find the combination of numbers in Excel to calculate a certain value. To do this, you can use the Solver Add-in feature and the SUMPRODUCT function.

Step 1: Open the Solver Add-in

1.First, you need to ensure that the Solver Add-in feature is active by selecting File > Options. From the Options dialog box, select Add-Ins > select Solver Add-in in the Inactive Application Add-ins section > Go.

2.The Add-ins dialog box will appear. Select Solver Add-in > OK to install the Add-in.

Step 2: Insert the SUMPRODUCT function

Let’s observe the Excel dataset below. The dataset has different numbers in column B. In column D, the user places the SUMPRODUCT function and the Result column in column C.

First, you need to enter the following formula into D5 and press ENTER. =SUMPRODUCT(C5:C10,B5:B10)

Then, cell D5 will give a result of 0.

Step 3: Using Solver Add-in

Firstly, select Solver in the Data tab.

Then, the Solver Parameters dialog box will appear, please follow these steps:

1. In the Set Objective option, select the cell $D$5 where the SUMPRODUCT formula is placed.
2. In the To field, select Value Of and enter the desired sum. Here, the user has entered 100 as the sum value.
3. In the By Changing Variable Cells field, select the range $C$5:$C$10 > click Add.
4. Select $C$5:$C$10 in the Cell Reference and select bin from the drop-down box > click OK.
5. In the Solver Parameters window, click Solve.
6. The Solver Result window will appear. Select Keep Solver Solution > Keep Solver Solution > click OK. Therefore, you will receive the result in binary form. The number 1 represents the numbers that add up to 100 (as shown below).

Step 4: Changing the Sum Value Let’s change the sum value by:

1. Selecting Solver in the Data tab.
2. In the To field, enter the sum value of 150 and click Solve.
3. Combination of numbers that add up to 150.