Worksheet not Saving array values

[RobF]

Below is the syntex I am using in the array... and the source file.. when opened will calculate the values. The problem arises when I close the source file and values return to this " #Value". My entire frontend file is nothing but arrays and links compiling data from multiple sources and everything works except cells that have the below array in them.

Can anyone help in figureing out why my cell values will not save upon saving and closing both my frontend file and source file?

=SUMIFS([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,[MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80,"FRIDAY",[MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80,TODAY()-3,[MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80,"First Chargeback")+SUMIFS([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,[MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80,"FRIDAY",[MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80,TODAY()-4,[MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80,"First Chargeback")+SUMIFS([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,[MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80,"FRIDAY",[MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80,TODAY()-5,[MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80,"First Chargeback")+SUMIFS([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,[MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80,"FRIDAY",[MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80,TODAY()-6,[MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80,"First Chargeback")+SUMIFS([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,[MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80,"FRIDAY",[MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80,TODAY()-7,[MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80,"First Chargeback")




Thank You in advance..........

Rob

 

[Marcelo Branco]

Hi Rob,

SUMIFS doesn't work with closed workbooks.

You should use SUMPRODUCT. Something like this

=SUMPRODUCT([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,--([MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80="FRIDAY"),--([MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80=TODAY()-3),--([MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80="First Chargeback")) + SUMPRODUCT(....)+...


Or, better, create a named range, say MyRange, containing
TODAY()-3
TODAY()-4
TODAY()-5
TODAY()-6
TODAY()-7


and use (untested)

=SUMPRODUCT([MIS_Incoming.xls]Weekly_Inc_Overview!$E$2:$E$80,--([MIS_Incoming.xls]Weekly_Inc_Overview!$B$2:$B$80="FRIDAY"),--ISNUMBER(MATCH([MIS_Incoming.xls]Weekly_Inc_Overview!$C$2:$C$80,MyRange,0)),--([MIS_Incoming.xls]Weekly_Inc_Overview!$D$2:$D$80="First Chargeback"))

Hope this helps.

M.

 

[RobF]

Thank you so much Marcelo,

That worked perfectly!!!

 

[Marcelo Branco]

Thank you so much Marcelo,

That worked perfectly!!!

You are welcome and thanks for the feedback.

M.