Formula's #Value when excel sheet updates Links

[Dtex20]

Hi,

I've got some SumIFS, and CountIFS when my sheet updates it's links to other sheets, that should have no impact on the forumla's the 2 previous mentioned formula's seem to corrupt.

The formula's work if i choose not to update the sheet, and also work if i open the sheet after i update the links.

Has anyone accounted a issue like this before, i'm thinking of swapping to VBA if i can't find a fix.

=COUNTIFS('*SheetName*Orders'!I3:I154116,"="&B338,'*SheetName*Orders'!H3:H154116,">="&C336,'*SheetName*Orders'!H3:H154116,"<="&P325)

=SUMIFS(''*SheetName*Orders'!$L$3:$L$154116,''*SheetName*Orders'!$I$3:$I$154116,"="&B338,''*SheetName*Orders'!$H3:$H$154116,">="&C336,''*SheetName*Orders'!$H3:$H$154116,"<="&D336)

Thanks!

 

[RoryA]

SUMIF(S) and COUNTIF(S) do not work with closed workbooks. If you need to work with closed workbooks, you should use SUMPRODUCT instead.

 

[Dtex20]

SUMIF(S) and COUNTIF(S) do not work with closed workbooks. If you need to work with closed workbooks, you should use SUMPRODUCT instead.

=SUMPRODUCT(--(*SheetName*$I:$I=B338),--(*SheetName*!$H:$H>=$C$336),--(*SheetName*$H:$H<=$D$336),*SheetName*$L:$L) - SumIFS
=SUMPRODUCT((((*SheetName*$I:$I=B339)*1)*(*SheetName*!$H:$H>=$C$336)*(*SheetName*$H:$H<=$D$336))) - CountIFS

Thanks very much man, i have shared the forumla's i used incase anyone else needs them.