Formula's #Value when excel sheet updates Links

Dtex20

Board Regular
Joined
Jan 29, 2018
Messages
50
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!
 

Some videos you may like

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
Joined
May 2, 2008
Messages
34,814
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
Joined
Jan 29, 2018
Messages
50
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,974
Messages
5,447,654
Members
405,460
Latest member
stuartbennett

This Week's Hot Topics

Top