In

CodePudding user response：

try this in cell G2:

=BYROW(F2:F,LAMBDA(fx,IF(fx="",,LAMBDA(cx,dx,{IF(cx-dx>0,cx-dx,),IF(dx-cx>0,dx-cx,)})(SUMIF(A:A,fx,C:C),SUMIF(A:A,fx,D:D)))))

-

CodePudding user response：

Use query(), like this:

=arrayformula( 
  lambda( 
    aggregated, 
    lambda( 
      account, balance, 
      { 
        "Account Type", "Dr", "Cr"; 
        account, 
        if( balance >= 0, balance, iferror(1/0) ), 
        if( balance < 0, balance, iferror(1/0) ) 
      } 
    )( 
      query(aggregated, "select Col1", 0), 
      query(aggregated, "select Col2", 0) 
    ) 
  )( 
    query(
      A3:D, 
      "select A, sum(C) - sum(D) 
       where A is not null 
       group by A 
       label sum(C) - sum(D) '' ", 
      0 
    ) 
  ) 
)

CodePudding user response：

You can set a QUERY that finds both entire columns like this:

=QUERY(A2:D,"SELECT A,SUM(C)-SUM(D),SUM(D)-SUM(C) where A is not null group by A",1)

And check with LAMBDA if there are negative values and change them to null:

=LAMBDA(a,INDEX(IF(a<0,,a)))(QUERY(A2:D,"SELECT A,SUM(C)-SUM(D),SUM(D)-SUM(C) WHERE A is not null group by A",1))