Defining something within an ArrayFormula which is then used in the ArrayFormula

Question

Thanks for taking some time out of your day.

So I have this code:

=ARRAYFORMULA(SUM(IF(Z$3:Z$294>Q349; IF(AB$3:AB$294>0; 0; IF(Z$3:Z$294>Q349; Y$3:Y$294-(F$3:F$294*Y$1); 0))))-(SUM(IF((J$3:J$294>0)*(Z$3:Z$294>Q349); F$3:F$294; 0))))

And I know this won’t work but it should be this:

=ARRAYFORMULA(SUM(IF(Z$3:Z$294>Q349; IF(AB$3:AB$294>0; 0; IF(Z$3:Z$294>Q349; Y$3:Y$294-(F$3:F$294*Y$1); 0))))-(SUM(AB$3:AB$294=IF((J$3:J$294>0)*(Z$3:Z$294>Q349); F$3:F$294; 0))))

I want to fill

AB$3:AB$294

With

IF((J$3:J$294>0)*(Z$3:Z$294>Q349); F$3:F$294; 0))))

But the tricky thing for me is, the fact that

AB$3:AB$294

Is used in the first part:

ARRAYFORMULA(SUM(IF(Z$3:Z$294>Q349; IF(AB$3:AB$294>0; 0; IF(Z$3:Z$294>Q349; Y$3:Y$294-(F$3:F$294*Y$1); 0)))

So I need a way to fill the array and use the values from that array within the same formula.

If somebody could help that would be amazing.

Thanks in advance,

Answer 1

use:

=ARRAYFORMULA(FLATTEN(BYCOL(IF(R2:R19>TRANSPOSE(K24:K74);
 IF(IF((H2:H19>0)*(R2:R19>TRANSPOSE(K24:K74)); D2:D19; 0)>0; 0;
 IF(R2:R19>TRANSPOSE(K24:K74); Q2:Q19-(D2:D19*TRANSPOSE(K24:K74)); 0)); 0);
 LAMBDA(x; SUM(x)))-BYCOL(IF((H2:H19>0)*(R2:R19>TRANSPOSE(K24:K74)); D2:D19; 0); 
 LAMBDA(x; SUM(x)))))