#### Dtex20

##### Board Regular
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!

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### RoryA

##### MrExcel MVP, Moderator
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

##### Board Regular
SUMIF(S) and COUNTIF(S) do not work with closed workbooks. If you need to work with closed workbooks, you should use SUMPRODUCT instead.
Code:
``````=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.

Last edited: