Function Problems

Use Excel SUMPRODUCT to Multiply and Sum Multiple Cell Ranges

Phi Hung VoPhi Hung Vo Nov 20, 2026 868 views

Question details

The user wants to replace a long multiplication formula with a concise formula that multiplies corresponding cells across multiple ranges and adds the results.

Use Excel SUMPRODUCT to Multiply and Sum Multiple Cell Ranges
Product
Microsoft Excel
Device & OS
not provided
Scenario
Calculate the total of products from corresponding cells in rows 7, 9, and 10 across columns D through R.
Observed behavior
The desired formula should calculate D7×D9×D10 through R7×R9×R10 and sum those products.
Before you start

Check that the ranges cover the same number of cells and that their corresponding values are numeric. Identify the cell where you want the total to appear.

Solution 1Recommended

Multiply Corresponding Ranges and Sum Them with SUMPRODUCT

Use SUMPRODUCT to replace separate multiplication terms with one formula.

1
Select the result cell

In Excel, click the empty cell where you want the total to appear.

2
Enter the formula

Type =SUMPRODUCT(D7:R7,D9:R9,D10:R10) into the cell or formula bar.

3
Calculate the total

Press Enter. Excel multiplies the corresponding values in the three ranges and adds the products.

Multiply Corresponding Ranges and Sum Them with SUMPRODUCT
Keep range dimensions consistent: All ranges must have the same dimensions. Depending on your regional settings, you may need semicolons instead of commas between arguments.
Calculate with WPS Spreadsheet

Multiply and Sum Ranges in WPS Spreadsheet

Use SUMPRODUCT in WPS Spreadsheet to calculate and total products from corresponding cells in multiple ranges.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to choose your Excel workbook.
  2. 2. Choose a result cell: Click the cell where you want to display the total.
  3. 3. Enter SUMPRODUCT: In the cell or formula bar, type =SUMPRODUCT(D7:R7,D9:R9,D10:R10), adjusting the ranges to match your data.
  4. 4. Calculate and save: Press Enter to calculate the result, then use File > Save to save the workbook.
Replace long multiplication formulas with SUMPRODUCTOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet formula bar and gridWork with spreadsheets in the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

What does SUMPRODUCT calculate in this example?

The formula =SUMPRODUCT(D7:R7,D9:R9,D10:R10) multiplies the three values in each column, then adds the products from columns D through R.

Do all SUMPRODUCT ranges need to be the same size?

Yes. Select ranges with matching dimensions, such as D7:R7, D9:R9, and D10:R10. Mismatched dimensions can produce a #VALUE! error.

What happens if a cell in a range contains text?

SUMPRODUCT treats nonnumeric entries in its array arguments as zero. Check the source cells if text or numbers stored as text make the total unexpected.

Why does Excel reject the commas in my formula?

Your regional settings may require semicolons as argument separators. Try =SUMPRODUCT(D7:R7;D9:R9;D10:R10) in the formula bar.