In this tutorial, we will learn how to calculate the sales value in Excel from the price and quantity sold information. This we will do by using the SUMPRODUCT function.
Let’s look at below example. Where we have the unit price information in Column C. The quantity sold on in January to June is in D to I column. Our requirement here is to calculate the sales value for every single month in the row number 10.
Follow these steps to calculate the sales value.
Step 1
In D10 cell, write this formula, =SUMPRODUCT($C$5:$C$9,D5:D9). Use the dollar symbol in price range to keep it constant, so that you can drag the same formula for other months.
Step 2
Press Enter, Click on the D10 cell, point to bottom right corner of cell, when + symbol appears, click and hold the mouse and drag and fill for other months.
This will calculate the sales values for each month.
See below illustration for how does this work.
SUMPRODUCT Function
As the name suggests, SUMPRODUCT calculates the SUM of the products. Product is the multiplication of two or more range. Here in this case too, we have to first multiply the each item’s quantity sold with the corresponding unit price, then add all the result of multiplication. This is possible with the dedicated Excel function “SUMPRODUCT”.
By using SUMPRODUCT we can multiply more than two range too, but we have to make sure that the length of every array is equal. If length is different, it throws #VALUE! error.
There so many advanced use of SUMPRODUCT function in Excel. Specially when we combine this function with other functions. This we will cover in upcoming tutorials.
If you want to practice this function, download the practice workbook file from below.
