I currently for this one working, which gives a balance per lot as per the total qty demanded by a client ABC of a certain product, but how can I turn this into an arrayformula or query instead?
=IF(C3="";"";IF(SUMIFS(E$3:E3;A$3:A3;A3;B$3:B3;B3)-C3<0;0;sumifs(E$3:E3;A$3:A3;A3;B$3:B3;B3)-C3))
Here is the file, if you want to jump in! https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing
You may try:
=map(A3:A;B3:B;C3:C;E3:E;lambda(Σ;Λ;Γ;Ξ;if(Σ="";;max(0;sumifs(E3:Ξ;A3:Σ;Σ;B3:Λ;Λ)-Γ))))