Techniques to accommodate new entries in google sheets

Question

As you can see I transpose codes into unique column headings so that debits and credits are analysed and summated. Summations are transposed in another sheet to create summary profit/loss account. I need help how to replicate the sum formula in column I to serve any expanded transposed unique codes and whether/how I should use arrayformula for the individual cell output.

EDIT

Actual output looks like this:

My problem is to how to automatically accommodate new entries/codes in the totals row and main body of cells. The data belongs to a residents' committee so I can only show anonymous data as image.

EDIT 2

Actual input is imported from bank records, then coded:

Can you share a sample sheet? Additionally, please include what would be the expected output starting from an example input. — Alessandro

Calculuswhiz Calculuswhiz · Accepted Answer · 2020-10-13T12:33:11

Query is pretty good for the SUM part.

Starting in column I, you can do:

=ArrayFormula(INDEX(QUERY(
  0+OFFSET(I4,0,0,ROWS(F6:F),COUNTA(UNIQUE(F4:F))),
  "select "&
  JOIN(
    ",",
    "sum(Col"&SEQUENCE(COUNTA(UNIQUE(F4:F)))&")"
  )
),2))

The 0+ or the VALUE in the second one (they both do the same thing here) transforms the data cells to default to 0 if blank, otherwise the query fails. This also lets us refer to the columns by sequence number, which is what we do in the second argument. We build the query into something that looks like select sum(Col1),sum(Col2),...,sum(ColN). Since this gives us a header by default, we could relabel everything in the query statement, but that gives too much extra code, so the easier thing to do is use INDEX to select the sums.

The EQ part is fairly straightforward to Arrayify. Starting in I4:

=ArrayFormula(
  (FILTER(F4:F,F4:F<>"")=FILTER(I2:2,I2:2<>""))*
  IF(
    Array_constrain(G4:G,COUNTA(FILTER(F4:F,F4:F<>"")),1),
    G4:G,
    -H4:H
  )
)

The FILTERs just filter out the blank cells, and the Array_Constrain sizes the G column to the same size as the filtered F column.

Techniques to accommodate new entries in google sheets

1 Answers