Adapt SUMPRODUCT formula to ignore #N/A values in target range

[jwalkerday]

[TABLE="width: 256"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]Result Flag[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD="align: right"]5678[/TD]
[TD="align: right"]14[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]=SUMPRODUCT(Sheet2!$B$3:$D$4*(Sheet2!$B$1:$D$1=B$1)*(Sheet2!$B$2:$D$2="Result")*(Sheet2!$A$3:$A$4=$A3))[/TD]
[/TR]
</tbody>[/TABLE]

I'm using the above SUMPRODUCT formula on a large dataset. In testing we found we occasionally get an #N/A in a few cells (never in the result column). This then causes the sum product formula to fail and return #N/A. We don't care about those #n/a values in the target range so I would like the sumproduct formula just to ignore them. Is there a way to do that in the formula by using something like ISNUMBER etc?

The other alternative I found online was to use VBA to remove all the n/a's after import from the database but this is not an option in this case.

NOTE- This post relates to https://www.mrexcel.com/forum/excel...heck-if-flagged-result-then-return-value.html but is a separate issue so I thought I would raise it in a separate post. However if you are looking for a SUMPRODUCT formula similar to this I would recommend checking out the other post.

 

[Special-K99]

Why dont you just fix the formulas so the N/As don't appear?

IFERROR(formula, "")

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(Sheet2!$B$1:$D$1=B$1,IF(Sheet2!$B$2:$D$2="Result",
     IF(ISNUMBER(Sheet2!$B$3:$D$4<strike></strike>),Sheet2!$B$3:$D$4<strike></strike>))))

​