Excel SUMPRODUCT is one of the most used function. Because of it’s versatility, it can be used in numerous ways. This function can handle array in a simple way, giving novices an option to use array without comprehending the structure of it. It can manipulate up to 25-30 arrays, requires list of array as argument; =SUMPRODUCT(array1, array2….). So you just need to specify range of array in your worksheet. Primarily it is used for adding and then multiply the values stored in array. This post will be putting some light on how to use this function in your spreadsheet.
Launch Excel 2010 and open a spreadsheet on which you want to apply SUMPRODUCT function. For Instance, we have included a spreadsheet, containing records of Item & it’s respective price and number of products to be delivered, and corresponding charges as; Items, Delivery, Price, and Delivery Charges, as shown in the screenshot below.
Now we want to find out Total amount to be paid by the customers, including all the charges from every city. For evaluating total amount, old-school method can be used, but it would be hectic to write one. We will be using SUMPRODUCT function to evaluate the Total amount. We will start off with including row label Total Amount beneath the table and in it’s adjacent cell we will be writing the formula.
For calculating total amount we will write formula like this;
The first argument in the formula is C2:D11, this marks up the location of array1, which contains Items & Delivery field, and the second parameter E2:F11 which contains items price and delivery charges.
For example we need this sort of calculation for desired result; (56*100)+(2*100) and so on. It will evaluate by, multiplying number of items sold with it’s respective Prices and Delivery with its Delivery charges, as shown in the screenshot below.
Now we will be changing the value into currency value. From Home tab, under Number group, click drop-down button, and click Currency.