I am trying to get this Arrayformula to work-CodePudding

So I am trying to get this Arrayformula to work so I can plot this formula instead of calculating every Y myself.

=arrayformula(Sum(IF(Z3:Z294>Y$1;IFS(AB3:AB294>0;0;Z3:Z294>Y$1;Y3:Y294-(F3:F294*Y$1));0)-(Sum(IF(and(J3:J294>0;Z3:Z294>Y$1);F3:F294;0)))))

It gives me the (correct) return of this part:

Sum(IF(Z3:Z294>Y$1;IFS(AB3:AB294>0;0;Z3:Z294>Y$1;Y3:Y294-(F3:F294*Y$1));0)

But it doesn't subtract the second part:

-(Sum(IF(and(J3:J294>0;Z3:Z294>Y$1);F3:F294;0)))))

I am quite new to extensive Excel/Sheets formulas so I have no idea how to get this to work, It is also quite weird that the second part doesn't add up even seperate from the first part. So this also doesn't work:

=arrayformula(Sum(IF(and(J3:J294>0;Z3:Z294>Y$1);F3:F294;0)))

I hope it makes some sense without any context, thanks in advance!

Have a great rest of your day,

P.S. Please ignore simple mistakes, I don't code that often in Sheets ;)

CodePudding user response：

AND is not supported. instead of

and(J3:J294>0;Z3:Z294>Y$1)

do this:

(J3:J294>0)*(Z3:Z294>Y$1)