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

[jwalkerday]

	2019	2019	2019
Result Flag			Result
1234	54	51	77
5678	14	#N/A	101
		#N/A
=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))

<tbody>
</tbody>

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

​