Help needed debugging SUMPRODUCT

[mrshl9898]

Hi,

I'm in the process of replacing SUMIFS with SUMPRODUCTS for closed workbooks and have hit an error I cannot solve. The offending formula is:

=SUMPRODUCT(--([MFT.XLSX]Perth!$5:$5=TEXT($U$1,"mmmm")),--([MFT.XLSX]Perth!$6:$6=$W$1),[MFT.XLSX]Perth!$21:$21)

It is returning DIV/0

I know --([MFT.XLSX]Perth!$5:$5=TEXT($U$1,"mmmm")),--([MFT.XLSX]Perth!$6:$6=$W$1), is working as similar formulas looking at rows other than 21 are working.

The cell in question in row 21 contains a SUM formula (ISNUMBER returns a 1), and fails still if I replace it with a fixed value.

Copying the formula or value to row 22 and replacing $21 with $22 returns a result.

Any suggestions how I can debug?

Thanks!

 

[mrshl9898]

Found it, there was a DIV0 in row 21 elsewhere.

 

[mrshl9898]

Looks like a reoccuring issue, there are many DIV0s in another sheet, any way to ignore them?