How to Multiply and Sum Corresponding Excel Values
Question details
The user needs to calculate a grand total by multiplying numbers across two distinct but corresponding Excel ranges (e.g., price and quantity) and summing the total of these products, while gracefully handling routes or fields that are unused.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a consolidated total from two columns of data where some rows might be missing data for certain service fields, without creating a new helper column.
- Observed behavior
- The goal is to obtain a single total sum of the products directly within a single cell, ignoring blank or unused service fields.
Ensure that the two ranges you plan to multiply have the exact same number of rows and columns, as mismatched array sizes will result in calculation errors.
Use the SUMPRODUCT Function (Traditional & Widely Compatible)
The most straightforward and universally compatible way to multiply corresponding ranges and add the results in Excel.
The SUMPRODUCT function takes two or more arrays, multiplies the corresponding numbers, and returns the sum of those products. It inherently treats empty cells as zeros, which zeroes out the product for that specific row without breaking the formula.
Click on the cell where you want the grand total to appear.
Type `=SUMPRODUCT(` to begin your calculation.
Select or type your first range (e.g., `B4:B9`) representing the first set of values to be multiplied.
Type a comma `,`, then select your second corresponding range (e.g., `C4:C9`).
Close the parenthesis and press Enter. The formula `=SUMPRODUCT(B4:B9, C4:C9)` will multiply B4 by C4, B5 by C5, etc., and sum all the results together.
Use SUM and TOCOL for Excel 365
A modern dynamic array approach specific to newer versions of Excel that easily ignores blank cells or calculation errors.
Easily Multiply and Sum Corresponding Values in WPS Spreadsheet
WPS Spreadsheet fully supports the SUMPRODUCT function and array formulas, allowing you to calculate grand totals without intermediate columns. It provides a lightweight, fast, and completely free environment to manage complex data.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open the workbook containing the ranges you want to multiply and sum.
- 2. Select the target cell: Click on the cell where you want to display the final calculated total.
- 3. Input the formula: Type `=SUMPRODUCT(B4:B9, C4:C9)` (adjust the cell references to match your actual data).
- 4. Press Enter: Press the Enter key to instantly view the final calculated sum. Unused service fields will safely be treated as zeros.
Frequently Asked Questions
Why does my SUMPRODUCT formula return a #VALUE! error?
This error typically occurs if the corresponding arrays or ranges do not have the exact same dimensions (e.g., one range spans 6 rows and the other spans 7). It can also happen if one of the ranges contains non-numeric text strings that cannot be multiplied.
Can I multiply more than two columns using SUMPRODUCT?
Yes, you can include multiple arrays separated by commas. For example, `=SUMPRODUCT(A1:A5, B1:B5, C1:C5)` will multiply the corresponding values across all three columns for each row and sum the final results together.
How do I handle empty cells when multiplying ranges?
The standard SUMPRODUCT function inherently treats completely empty cells as zeros. This results in a zero product for that specific row, which does not affect your final grand total. If your cells contain formulas that return empty strings (""), you may need to use double negatives (--) to coerce them into numbers.
