In one of our recent trainings of retailers a good participant asked this question: I have data of ItemID, ItemName and Price in my worksheet. I also have an empty column with the header ‘Quantity’. Now the user should be able to enter any quantity and the final price of the total order should appear in another worksheet.
You can solve this problem using the ‘sumproduct’ function. If you wish to do this without a macro, you can enter the function in the cell of the worksheet where you wish to get the answer by accessing the data appropriately as shown in the video where we have a workbook with 2 worksheets called ‘data’ and ‘calculations.
The other option is to create a command button and in the code for the command button you again use the sumproduct function.
The function and the macro code for the command button are given below:
Private Sub CommandButton1_Click()
Range(“f6”) = “=SumProduct((c2:c46),(d2:d46))”
Watch the video to see the solution :