When it comes to performing calculations and analysis in Excel, numerous functions can save you time and effort, one of which is the SUMPRODUCT function.
In this blog post, we’ll explore the SUMPRODUCT function and its workings and provide a step-by-step guide for creating a SUMPRODUCT formula in Excel using examples.
Understanding the SUMPRODUCT Function in Excel
The SUMPRODUCT function might not be as commonly used as some of the more popular functions in Excel, but it’s an incredibly handy tool for complex calculations involving multiple arrays of data.
This function is designed to perform the sum of the products of corresponding values in arrays or ranges. In other words, it allows you to multiply values from different ranges and then sum up the results of those multiplications.
Doing this not only saves you a lot of time but can also free up space on a worksheet. This is because it eliminates the need for total columns for each set of data.
Advantages of Using SUMPRODUCT in Excel
Using the SUMPRODUCT function offers several advantages:
- No Need for Intermediate Columns: Unlike traditional methods that might require additional columns to store intermediate calculations, SUMPRODUCT lets you perform calculations directly without cluttering your spreadsheet.
- Efficient Data Analysis: When dealing with large datasets, SUMPRODUCT helps you efficiently perform calculations across multiple arrays, saving time and resources.
- Simplicity: Once you understand the concept, creating SUMPRODUCT formulas is straightforward and doesn’t require extensive Excel expertise.
Calculating Total Cost Price using the SUMPRODUCT function in Excel
Let’s consider a scenario where you have a dataset containing information about sales, including the cost price and the quantity sold for each item. Traditionally, you might be inclined to create an additional column to calculate the total cost for each row before finding the overall total. However, SUMPRODUCT can help you achieve this without needing extra columns.
Here’s how you can use the SUMPRODUCT function to calculate the total cost price for all sales in the given dataset:
1. Open Excel. Open the Excel spreadsheet containing your sales data. For this example, we opened the SUMPRODUCT spreadsheet.
2. Identify the Ranges. Ensure you have a column for ‘Cost Price‘ and another for ‘Quantity Sold’. Make sure these columns have data in the rows you’re interested in. For this example, our ‘Cost Price’ column is column G (this holds the value of the item’s cost), and our ‘Quantity Sold’ values are in column I (these values represent how many times the item was sold).
3. Select a Cell. Choose a cell where you want to display the total cost. For instance, let’s use cell E1.
4. Enter the Formula. In cell E1, enter the following SUMPRODUCT formula:=SUMPRODUCT(G6:G25, I6:I25)
In this formula, G6:G25 represents the range of cost prices, and I6:I25 represents the range of quantities sold. Adjust the ranges as per your actual data.
Note: In our example, the values in column G are multiplied by column I and added to the sum. For example, the value in cell G6 ($0.85) is multiplied by the value in cell I6 (62), which is added to the sum value in cell E1. This pattern continues down the range of data to give you the total sum value (total).
5. Press Enter. After entering the formula, press Enter. Excel will perform the necessary calculations and display the total cost for all sales in the selected range.
Create a SUMPRODUCT formula using the Function Arguments box to calculate the Sales Price
Now that we’ve calculated Total Cost Price using SUMPRODUCT, we can use the same process to total other columns. In this example, I’ll show you another way to create a SUMPRODUCT formula easily. In this method, we will use the Function arguments box.
We will look at how to total the Sale Price column data from column H by multiplying it by the Quantity sold in column I.
1. Select another cell (for example, E2). This will be the cell in which the total sum of the Sale Price will be displayed.
2. Click the fx (formula) button next to the formula box.
3. Find the SUMPRODUCT option in the Select a function field.
Note: If you cannot find SUMPRODUCT in the Select a function field, type it into the Search for a function box. Then select Go.
4. Select OK.
A Function Arguments box should have appeared.
5. While clicked into the Array 1 box, click and drag your mouse over the range for the Sale Price. In this example, that’s cells H6 to H25.
6. For the second Array, while clicked into the Array 2 box, click and drag your mouse over the range for the Quantity Sold. In this example, that’s cells I6 to I25.
Note: Always make sure the Arrays have the same dimensions. For example, the selected ranges must start and end in the same row.
7. Click OK.
The total sum of the Sale Price should now be displayed in cell E2.
Calculating the difference between Cost Price and Sales Price
Now that we have used SUMPRODUCT to calculate the Total Cost Price and the Total Sales Price, we can find the difference between the two.
Calculating the Difference
To find the difference between the total sale price and the total cost:
1. Select a cell (e.g., E3).
2. Enter the formula: =E2-E1 (assuming E2 contains the total sale price and E1 contains the total cost).
3. Press Enter to calculate and display the difference.
SUMPRODUCT formula in Excel: Example 1
In this example, we have a spreadsheet showing how many litres of a particular product a company has in stock. The litres of the product can be seen in cells C4, D4, E4, F4, and G4, and the amount of product in stock is in columns C-H, rows 5-14. The total litres available is seen in column H.
Upon inspection, it can be seen that whoever created this spreadsheet also created an extensive formula to calculate the total litres, which can be seen in column H.
The formula created multiplied the amount of the items they had in stock (seen in row 5) by the number of litres of each individual product (seen in row 4). The total column ended up with the formula = $C5*$C$4+$D5*$D$4+E5*$E4+$F5*$F$4+G5*$G$4
While this formula does, in fact, work, it is very messy and also requires a lot of time to set up.
This is where SUMPRODUCT comes in. It saves a lot of time, as you can select the ranges of what you want to total. In this example, we created the formula =SUMPRODUCT(C$4:G$4,C5:G5) in cell I6 and copied it down the column.
This formula multiplies the size of the product in litres (cells C4-G4) by the number of items in stock (cells C5-G5). The $ has been added to the formula (between the C and 4 and the G and 4) so that when the formula is copied, the reference to the quantities row doesn’t creep.
Using SUMPRODUCT in this example allows for a quicker and easier formula creation.
SUMPRODUCT Formula in Excel: Example 2
In this example, we have a spreadsheet of a client with multiple stores they buy for. The worksheet collates purchases for all stores into one sheet. The unit price for what they sell can be seen in column C, and the stores they sell to are in columns D, E, F, G and H.
Similar to the first example, this spreadsheet’s total sum (in row 15 of cells D, E, F, G and H) is super extensive and messy. The formula =$C5*D5+$C6*D6+$C7*D7+$C8*D8+$C9*D9+$C10*D10+$C11*D11+$C12*D12+$C13*D13+$C14*D14 is used to calculate the total.
In our example, using SUMPRODUCT makes this calculation more streamlined and easier.
By using the formula =SUMPRODUCT($C20:$C29,D20:D29) in the total cell at the bottom of the column and then clicking and dragging this formula along the row, all of the totals for each store are quickly and easily summed.
Watch the SUMPRODUCT Tutorial
[Watch on YouTube] / [Subscribe to our YouTube Channel]
Conclusion
In conclusion, the SUMPRODUCT function is a valuable tool in Excel for performing complex calculations involving multiple arrays or ranges of data. It enables you to multiply corresponding values and sum up the results, all in a single formula. By following the steps outlined in this blog post, you can harness the power of SUMPRODUCT for efficient data analysis in your Excel spreadsheets.
Was this post helpful? Let me know in the comments below.
Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.
Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.
And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!”
Happy Excel-ling!!