Use Excel SUMPRODUCT to Multiply Ranges and Ignore Blanks
Question details
Calculate the sum of corresponding values multiplied across two ranges while ignoring blank cells.

- 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.
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.
Use Comma-Separated Ranges in Excel SUMPRODUCT
Use separate range arguments to calculate the total while treating nonnumeric entries as zero.
In Excel, click the cell where you want the weighted total to appear.
Type =SUMPRODUCT(A2:A4,B2:B4), replacing A2:A4 and B2:B4 with your equally sized ranges.
Press Enter. Excel multiplies corresponding numeric values and sums the results; nonnumeric entries in the range arguments are treated as zero.
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.

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. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Select a result cell: Click an empty cell where the weighted total should appear.
- 3. Enter SUMPRODUCT: Type =SUMPRODUCT(A2:A4,B2:B4), substituting your own equally sized ranges.
- 4. Show and save the result: Press Enter to calculate the total, then use File > Save to save the workbook.

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




