Dynamic formula to get data from another file

[leprince2007]

Hello everybody,
Could you help me in my question ,please?
My question:
I need to create one dynamic formula to get data of company and its sum of quantity from another file (Data file) and put the results on(Report file) on 2 columns (like in the file ) depending on 2 conditions :
1-Date
2-Material
Note:I need one (not two) dynamic formula only to get the data( company& sum of quantity) .I use office 365.
No helper columns.
see the attachment

[DJunqueira]

Proposed solution.

To lock the range I transformed your table in 'Data (1)' into a named table. Formula is in [Report.xlsx]Report!$E$10.

Please Login or Register  to view this content.

[leprince2007]

Thanks DJunqueira for your solution.But I need quantity to be summed.
Sorry I forget to mention that.
I edited my first thread.

[Bo_Ry]

Try

=FILTER(FILTER(Data.xlsx!$C$2:$G$10,{1,0,0,0,1}),(Data.xlsx!$A$2:$A$10=B5)*(Data.xlsx!$B$2:$B$10=C5))

[leprince2007]

Thanks Bo_Ry for your solution.But I need quantity to be summed.
Sorry I forget to mention that.
I edited my first thread.

[DJunqueira]

For what you want I propose two dynamic array formulas, because find text and also add in one function kind of complex.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[leprince2007]

Thanks DJunqueira for your solution.But I need one formula only. I tried to implement it but failed.
Please help me

[Bo_Ry]

Last try

=LET(dl,Data.xlsx!$A$2:$A$10,d,B5,ml,Data.xlsx!$B$2:$B$10,m,C5,cl,Data.xlsx!$C$2:$C$10,qty,Data.xlsx!$G$2:$G$10,c,UNIQUE(FILTER(cl,(dl=d)*(ml=m))),CHOOSE({1,2},c,SUMIFS(qty,dl,d,ml,m,cl,c)))

[leprince2007]

Thanks Bo_Ry for your solution but this formula gives me "Value" error when I open report file alone
I think this happens because "sumifs" doesnot work with closed files

[Bo_Ry]

try

=LET(dl,Data.xlsx!$A$2:$A$10,d,B5,ml,Data.xlsx!$B$2:$B$10,m,C5,cl,Data.xlsx!$C$2:$C$10,qty,Data.xlsx!$G$2:$G$10,
c,UNIQUE(FILTER(cl,(dl=d)*(ml=m),)),CHOOSE({1,2},c,MMULT((TRANSPOSE(dl)=d)*(TRANSPOSE(ml)=m)*(c=TRANSPOSE(cl)),qty)))

[leprince2007]

Thank you very much.
it worked like charm