Formula string more than 255 characters not work when the source file is closed

[Osama Fawzy]

Hi, I have the some formulas (such as the below), them linked in between 3 files and doesn't work and return #VALUE! when the source file closed and return the right value when the source file is opening

=IF(NOT(B2="SU-NMG"),SUMIFS('Z:\Planning & Records Depratments\Aircrafts Technical Data\1-Aircraft''s Status\4-SU-NML\[SU-NML_2022.xlsb]AC TLB'!$C$7:$C$1199,'Z:\Planning & Records Depratments\Aircrafts Technical Data\1-Aircraft''s Status\4-SU-NML\[SU-NML_2022.xlsb]AC TLB'!$B$7:$B$1199,">="&J2,'Z:\Planning & Records Depratments\Aircrafts Technical Data\1-Aircraft''s Status\4-SU-NML\[SU-NML_2022.xlsb]AC TLB'!$B$7:$B$1199,"<="&L2),SUMIFS('[SU-NMG_2022.xlsb]AC TLB'!$C$7:$C$1199,'[SU-NMG_2022.xlsb]AC TLB'!$B$7:$B$1199,">="&J2,'[SU-NMG_2022.xlsb]AC TLB'!$B$7:$B$1199,"<="&L2))

 

[Fluff]

Sumifs does not work with closed workbooks, you will need to use sumproduct instead.

 

[Osama Fawzy]

Thank u, but i tried to use sumproduct , but i can't, please if u know tell me how

 

[Fluff]

Try

=IF(NOT(B2="SU-NMG"),SUMPRODUCT('Z:\Planning & Records Depratments\Aircrafts Technical Data\1-Aircraft''s Status\4-SU-NML\[SU-NML_2022.xlsb]AC TLB'!$C$7:$C$1199,('Z:\Planning & Records Depratments\Aircrafts Technical Data\1-Aircraft''s Status\4-SU-NML\[SU-NML_2022.xlsb]AC TLB'!$B$7:$B$1199>=J2)*('Z:\Planning & Records Depratments\Aircrafts Technical Data\1-Aircraft''s Status\4-SU-NML\[SU-NML_2022.xlsb]AC TLB'!$B$7:$B$1199<=L2)),SUMPRODUCT('[SU-NMG_2022.xlsb]AC TLB'!$C$7:$C$1199,('[SU-NMG_2022.xlsb]AC TLB'!$B$7:$B$1199>=J2)*('[SU-NMG_2022.xlsb]AC TLB'!$B$7:$B$1199<=L2)))

 

[Osama Fawzy]

many thanks,

 

[Fluff]

You're welcome & thanks for the feedback.