Function Problems

Use Excel SUMPRODUCT to Multiply Ranges and Ignore Blanks

Natalie TaylorNatalie Taylor Nov 20, 2026 868 views

Question details

Calculate the sum of corresponding values multiplied across two ranges while ignoring blank cells.

Multiply Excel Ranges and Ignore Blanks with SUMPRODUCT
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants a weighted total from two ranges in a single cell.
Observed behavior
The desired result is a sum of row-by-row products without errors caused by blank cells, text, or empty strings.
Before you start

Identify the two ranges to multiply and make sure they have the same dimensions. Check whether any cells contain actual error values, such as #N/A, which SUMPRODUCT does not automatically ignore.

Solution 1Recommended

Use Comma-Separated Ranges in Excel SUMPRODUCT

Use separate range arguments to calculate the total while treating nonnumeric entries as zero.

1
Select the result cell

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

2
Enter the formula

Type =SUMPRODUCT(A2:A4,B2:B4), replacing A2:A4 and B2:B4 with your equally sized ranges.

3
Calculate the total

Press Enter. Excel multiplies corresponding numeric values and sums the results; nonnumeric entries in the range arguments are treated as zero.

4
Replace a multiplication expression if needed

If your existing formula is =SUMPRODUCT(G5:G11*N5:N11), change it to =SUMPRODUCT(G5:G11,N5:N11) to avoid errors caused by text or formula-generated empty strings.

Use Comma-Separated Ranges in Excel SUMPRODUCT
Check regional separators: Some Excel regional settings use semicolons instead of commas to separate function arguments.
Calculate in WPS Spreadsheets

Calculate Weighted Totals with SUMPRODUCT in WPS Spreadsheets

Use SUMPRODUCT in WPS Spreadsheets to multiply corresponding ranges and return their total in one cell.

  1. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Select a result cell: Click an empty cell where the weighted total should appear.
  3. 3. Enter SUMPRODUCT: Type =SUMPRODUCT(A2:A4,B2:B4), substituting your own equally sized ranges.
  4. 4. Show and save the result: Press Enter to calculate the total, then use File > Save to save the workbook.
Calculate weighted totals from corresponding ranges.Use comma-separated range arguments to handle nonnumeric entries.Open and edit Microsoft Excel XLSX and XLS files.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMPRODUCT return #VALUE! when I multiply ranges?

A formula such as =SUMPRODUCT(A2:A4*B2:B4) can return #VALUE! when a range contains text or an empty string. Try =SUMPRODUCT(A2:A4,B2:B4) instead.

Do the ranges in SUMPRODUCT need to be the same size?

Yes. Select ranges with matching dimensions, such as A2:A4 and B2:B4; mismatched ranges can produce a #VALUE! error.

Does SUMPRODUCT ignore cells containing #N/A?

No. The comma-separated formula handles nonnumeric entries, but actual error values such as #N/A can still propagate. Check and correct those cells before calculating.

What if Excel does not accept commas in my formula?

Your regional settings may require semicolons as argument separators. Try =SUMPRODUCT(A2:A4;B2:B4).