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

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,

CodePudding user response：

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)))))