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:

  1. 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.
  2. Efficient Data Analysis: When dealing with large datasets, SUMPRODUCT helps you efficiently perform calculations across multiple arrays, saving time and resources.
  3. 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. 

Sales data worksheet in Excel

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).

Excel Sales Data spreadsheet with the columns Cost Price and Quantity sold shown in red boxes

3. Select a Cell. Choose a cell where you want to display the total cost. For instance, let’s use cell E1.

Excel Sales Data spreadsheet with red arrow pointing to cell E1

4. Enter the Formula. In cell E1, enter the following SUMPRODUCT formula:=SUMPRODUCT(G6:G25, I6:I25)

Excel Sales Data spreadsheet with Cost Price and Quantity sold columns selected and the formula =SUMPRODUCT(G6:G25,I6:I25) in cell E1

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.

Excel Sales Data spreadsheet with cell E1 shown in a red box and $11,550.33 in the cell

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.

Excel Sales Data spreadsheet with columns Sale Price and Quantity sold shown in red boxes

1. Select another cell (for example, E2). This will be the cell in which the total sum of the Sale Price will be displayed.

Sales Data spreadsheet in Excel with arrow pointing towards cell E2

2. Click the fx (formula) button next to the formula box.

Excel Sales Data spreadsheet with the fx button shown in a red box

3. Find the SUMPRODUCT option in the Select a function field.

Insert Function dialog box with Select a function: shown in a red box

Note: If you cannot find SUMPRODUCT in the Select a function field, type it into the Search for a function box. Then select Go.

Insert Function dialog box with SUMPRODUCT written in the Search for a function: box and shown in a red box
Inser Function dialog box with SUMPRODUCT written in the Search for a function: box and a red box around the option Go

4. Select OK.

Insert Function dialog box with SUMPRODUCT written in the Select a function: box and a red box around the option OK

A Function Arguments box should have appeared.

Function Arguments dialog box with cell E2 selected and a SUMPRODUCT formula being created in the cell

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.

Function Arguments box open with ranges H6:H25 in the Array1 box

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.

Function Arguments box open with ranges I6:I25 in the Array2 box

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.

Function Arguments box with ranges in Array1 and Array2 and a red box around the option OK

The total sum of the Sale Price should now be displayed in cell E2.

Sales Data excel spreadsheet with $13,946.32 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).

Sales Data Excel spreadsheet with red arrow pointing to cell E3

2. Enter the formula: =E2-E1 (assuming E2 contains the total sale price and E1 contains the total cost).

The formula =E2-E1 is in cell E3

3. Press Enter to calculate and display the difference.

Cell E3 has the value $2,395.99 and is shown in a red box

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.

Sales Data spreadsheet in Excel in the SUMPRODUCT 2 tab

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.

Excel Data Sales in spreadsheet SUMPRODUCT 2 and a long formula in cell H5

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.

Excel Sales data in SUMPRODUCT 2 spreadsheet and a SUMPRODUCT formula in cell I5

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.

Excel Sales Data in SUMPRODUCT 3 spreadsheet

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.

Excel Sales Data in spreadsheet SUMPRODUCT 3 and the total cell D15 showing a long formula

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.

Excel Sales Data in spreadsheet SUMPRODUCT 3 and total cells C15-H15 shown in a red box

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.


Sharyn Baines
Sharyn Baines
Certified Microsoft Office Specialist

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!!

Leave a Reply

Your email address will not be published. Required fields are marked *