Arrayformula replacement for sumifs formula with date range and other criteria-CodePudding

In this

CodePudding user response：

You can use this formula:

    =MAKEARRAY(COUNTA(G3:G),2,LAMBDA(r,c,LAMBDA(sums,
IF(AND(sums>0,c=1),sums,IF(AND(sums<0,c=2),-sums,"")))
(SUMIFS($C$3:$C,$D$3:$D,INDEX(G3:G,r),$A$3:$A,">="&$H$1,$A$3:$A,"<="&$I$1)-SUMIFS($C$3:$C,$E$3:$E,INDEX(G3:G,r),$A$3:$A,">="&$H$1,$A$3:$A,"<="&$I$1))))

CodePudding user response：

delete all your formulae in H3:I range and use this in H3:

=INDEX(LAMBDA(d, c, {IF(d>c, d-c, ), IF(c>d, c-d, )})
 (QUERY({A3:D},       "select sum(Col3) 
 where Col1 >= date '"&TEXT(H1, "e-m-d")&"' 
   and Col1 <= date '"&TEXT(I1, "e-m-d")&"' group by Col4 label sum(Col3)''"), 
 QUERY({A3:C, E3:E}, "select sum(Col3) 
 where Col1 >= date '"&TEXT(H1, "e-m-d")&"' 
   and Col1 <= date '"&TEXT(I1, "e-m-d")&"' group by Col4 label sum(Col3)''")))

CodePudding user response：

UPDATED FORMULA:

=MAP(BYROW(G3:G,LAMBDA(gx,IF(gx="",,SUM(IFERROR(FILTER(C:C,A:A>=H1,A:A<=I1,D:D=gx)))))),BYROW(G3:G,LAMBDA(gx,IF(gx="",,SUM(IFERROR(FILTER(C:C,A:A>=H1,A:A<=I1,E:E=gx)))))),LAMBDA(ax,bx,IF(ax="",,IF(ax-bx>0,{ax-bx,IFERROR(1/0)},{IFERROR(1/0),-(ax-bx)}))))