How to use the SUMPRODUCT function in a formula in Excel
The SUMPRODUCT Function is one of the most time saving functions in all of Excel especially when you have a boatload of data to work with. What it does is total the products returned by multiplying the values in corresponding arrays. For one array to correspond to another, each must consist of the same number of rows and columns. If you don’t understand, don’t worry as we are going to use an example. In this example, the costs of all products in a supermarket have to be summed up to obtain the value of the entire inventory. The syntax for SUMPRODUCT is: SUMPRODUCT(array1, array2, …)
array1, array2, …: From 2 to 30 arrays whose components to be multiplied and then added.
How to Calculate the Supermarket’s Inventory
- Enter the data shown in Picture below or use your own format. The quantity of each product is listed along with the cost of each unit.
- Select cell B12 and type the following formula: =SUMPRODUCT(B2:B10,A2:A10).
- To check that this result is correct, select cells D2:D10 and type the following formula: =A2*B2.
- Press CTRL+Enter and then Sum the same range in cell D12.
You should now be set!
