SUMPRODUCT that ignores #N/A values

Hi,

I am using a sumproduct as a SUMIF across two separate workbooks - Data is in one and the sumproduct is in a dashboard on the other. I need to sum one column - AE if a value from cell B1 exists in column C of the source data and then / it by the same sum of column AF to produce a % end result. my formula works fine but there are #N/A in the source data (which is a report I don't own) so I can't remove them. the formula I am using is:

=SUMPRODUCT((--('[DLS Error Report 2019 MASTER.xlsx]Error Summary By Branch'!$C$7:$C$851=$B$1)),('[DLS Error Report 2019 MASTER.xlsx]Error Summary By Branch'!$AE$7:$AE$851))/SUMPRODUCT((--('[DLS Error Report 2019 MASTER.xlsx]Error Summary By Branch'!$C$7:$C$851=$B$1)),('[DLS Error Report 2019 MASTER.xlsx]Error Summary By Branch'!$AF$7:$AF$851))


How can I get this to IGNORE the N/A values in column C? there are no N/A values in columns AF and AE.

Thanks

Jen

I'm assuming that the DLS file will be closed at points, which is why you're using SUMPRODUCT in first instance

try switching to an Array

Formula:

Please Login or Register  to view this content.

Hi XLent, yes this is correct the DLS file will be closed when I am pulling the data from it. This array formula works perfectly thank you.

Jen