Function Problems

How to Sum the Products of Two Columns in Excel

Guest Writer Oct 10, 2026 871 views

Question details

The user needs a formula to multiply values across two columns row-by-row and sum the resulting products.

How to Sum the Products of Two Columns in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating a total sum from two related data columns, such as a quantity column and a price column.
Observed behavior: Adding the columns separately using standard addition does not calculate the correct row-by-row product.

Before you start

Ensure that both columns contain numerical data without any text mixed in, and verify that the two ranges are exactly the same size to avoid formula errors.

Solution 1Recommended

Use the SUMPRODUCT Function

The SUMPRODUCT function is the most direct and efficient method to multiply corresponding values across two ranges and sum the results without needing extra columns.

This formula multiplies the values in corresponding rows (e.g., C5 multiplied by D5) and then adds up all the results into a single total. It completely eliminates the need to create additional calculation columns for subtotals.

Select the target cell

Click on the cell where you want the grand total to appear.

Enter the formula

Type =SUMPRODUCT(C5:C65,D5:D65) into the formula bar. Adjust the cell references to exactly match the rows in your actual data ranges.

Calculate the result

Press the Enter key. Excel will automatically calculate the sum of all row-by-row multiplications.

Formula Tip: If you ever need to simply add the two ranges together without multiplying the corresponding rows, use the basic =SUM(C5:C65,D5:D65) formula instead.

Solution 2

Use a Helper Column for Row-by-Row Calculation

If you need to see the individual product for each row before calculating the grand total, using a helper column with a basic SUM function is the best approach.

Manage Spreadsheets Easily

Use WPS Spreadsheet to Handle Complex Formulas Effectively

WPS Spreadsheet is a powerful, highly compatible tool for all your data calculation needs. It natively supports advanced array functions like SUMPRODUCT, allowing you to seamlessly calculate total costs, manage inventory, and process large datasets without performance lag.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the document containing your data.
2. Input the formula: Click on your desired total cell and type =SUMPRODUCT(.
3. Select the ranges: Highlight your first column range, type a comma, highlight the second column range, and type a closing parenthesis.
4. Get your total: Press Enter to instantly calculate the sum of the products.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Natively supports over 400 advanced formulas including SUMPRODUCT and matrix calculations.Lightweight software that runs smoothly even on older devices.Provides a familiar tabbed interface for a seamless user transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMPRODUCT formula return a #VALUE! error?

A #VALUE! error typically occurs if the two selected column ranges are not the same size (for example, comparing C5:C65 with D5:D60). It can also happen if there is standard text in the data cells instead of numbers. Ensure your ranges match perfectly and contain only numerical data.

Can I use SUMPRODUCT with more than two columns?

Yes, the SUMPRODUCT function can handle multiple arrays. For example, using =SUMPRODUCT(A1:A10, B1:B10, C1:C10) will multiply the corresponding values across all three columns row-by-row and then add up the results into a single sum.

What is the primary difference between SUM and SUMPRODUCT?

The SUM function simply adds all the individual numbers within the selected ranges together. In contrast, SUMPRODUCT first multiplies the corresponding cells across the selected ranges against each other, and then adds those calculated products together into a grand total.

Excel & Data Problems