When it comes to performing calculations and analysis in Excel, there are numerous functions that can save you time and effort and one of these is the **SUMPRODUCT** function.

In this blog post, we'll dive into what the **SUMPRODUCT **function is, how it works, and provide a step-by-step guide on how to create a **SUMPRODUCT **formula in Excel using examples.

- Understanding the SUMPRODUCT Function in Excel
- Advantages of Using SUMPRODUCT in Excel
- Calculating Total Cost Price using the SUMPRODUCT function in Excel
- Create a SUMPRODUCT formula using the Function Arguments box to calculate the Sales Price
- Calculating the difference between Cost Price and Sales Price
- SUMPRODUCT formula in Excel: Example 1
- SUMPRODUCT formula in Excel: Example 2

## 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 when dealing with complex calculations that involve 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 it can save so much space on a worksheet. This is because it eliminates the need for you to have 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 the need for 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. Start by opening 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 column 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 cost of the item) 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 being multiplied by column I and added to the sum. For example, the value in cell G6 ($0.85) is being multiplied by the value in cell I6 (62) and this value is being 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 another columns sum. However, we want to show you another way you can easily create a **SUMPRODUCT **formula. In this method we will be using the Function arguments box.

For example, we will look at how to total the **Sale Price** columns data in 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, you can 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 e.g. the selected ranges must start and end at the same row.

**7.** Click **OK**.

The **Sale Price** total sum 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 amount of 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 so much time as you are able to 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 we copied this down the column.

This formula is multiplying the size of the product in litres (cells C4-G4) by the amount of items in stock (cells C5-G5). The $ have 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 creation of the formula.

## SUMPRODUCT Formula in Excel: Example 2

In this example, we have a spreadsheet of a client who has multiple stores they buy for. The worksheet collates purchases for all stores into one sheet. The unit price for what they are selling can be seen in column C and the stores they are selling 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.

## 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 then 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 us know in the **Comments **below.