Modify sumproduct formula to ignore #value! in range

1
votes

The formula I want to use is

=sumproduct(E3:E24, H3:H24)

but the problem is that both columns contain #VALUE! errors in various rows. (In particular: if an error appears in E14, it will appear in all cells across E14:H14.)

How do I modify the formula so that it returns the SUMPRODUCT of only the numeric values.

Here's a screenshot of the range I'm working with:

2
excelexcel-formulasumproduct
or you can use IFERROR to fix your source data and then use SUMPRODUCT as normal :) – Terry W

2 Answers

2
votes

Use an array version of SUMPRODUCT with IFERROR:

=SUMPRODUCT(IFERROR(E3:E24,0),IFERROR(H3:H24,0))

Using IFERROR this way requires the formula to be Array Entered with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

1
votes

IFERROR should replace the error with a 0:

=sumproduct(IFERROR(E3:E24,0),IFERROR(H3:H24,0))