CODEWITHSUNDEEP
google-sheetsarray-formulas
tedioustortoise
tedioustortoise

Reputation: 269

Arrayformula replacement for sumifs formula with date range and other criteria

In this spreadsheet:

Columns H and I give the sum of Dr or Cr for each Account within a date range.

I am looking for an arrayformula which replaces these so that if a new Account is added in column G (e.g."Account 4") then the arrayformula would calculate the totals for this new Account, instead of having to copy the existing =sumifs formula down.

enter image description here

Upvotes: 0

Views: 302

Answers (3)

player0
player0

Reputation: 1

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

enter image description here

UPDATE:

=INDEX(LAMBDA(x, {INDEX(x,,1), {D2:E2; QUERY({
 IF(INDEX(x,,2)<INDEX(x,,3), INDEX(x,,3)-INDEX(x,,2), ), 
 IF(INDEX(x,,3)<INDEX(x,,2), INDEX(x,,2)-INDEX(x,,3), )}, "offset 1", )}})
 (QUERY({A3:D, IFERROR(D3:D/0, D2); A3:C, E3:E, IFERROR(E3:E/0, E2)},
 "select Col4,sum(Col3) 
  where Col1 >= date '"&TEXT(H1, "e-m-d")&"' 
    and Col1 <= date '"&TEXT(I1, "e-m-d")&"' 
  group by Col4 
  pivot Col5")))

enter image description here

Upvotes: 1

rockinfreakshow
rockinfreakshow

Reputation: 29982

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

-

enter image description here

Upvotes: 1

Mart&#237;n
Mart&#237;n

Reputation: 10177

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

enter image description here

Upvotes: 1

Related Questions