CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheetarray-formulascumulative-sum
Chris Barrett
Chris Barrett

Reputation: 601

Arrayformula for total in simple cashbook

I'm trying to create a simple arrayformula to keep a running tally in a cashbook. Pretty simple but got me stumped. Heres a basic table:

A B C
1 IN OUT TOTAL
2
3 10 10
4 1 9
5 7 16
6 5 21
7 4 17

So for example to generate the total in Cell C3 I use the formula offset(C3,-1,0)-B3+A3 but I thought I would then just be able to wrap it in arrayformula so it became arrayformula(offset(C3:C,-1,0)-B3:B+A3:A) but it errors and needs an extra row which just goes on endlessly.

Any help would be great.

Upvotes: 1

Views: 149

Answers (4)

player0
player0

Reputation: 1

use:

=ARRAYFORMULA(IF((A2:A<>"")+(B2:B<>""),
 MMULT(TRANSPOSE((ROW(B2:B)<=
 TRANSPOSE(ROW(B2:B)))*IF(A2:A="", -B2:B, A2:A)), 
 SIGN(IF(A2:A="", -B2:B, A2:A))^0), ))

enter image description here

or:

=ARRAYFORMULA(IF((A2:A<>"")+(B2:B<>""),
 MMULT(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*
 IF(A2:A="", A2:A-B2:B, A2:A-B2:B)), 
 SIGN(IF(A2:A="", B2:B, A2:A))^0), ))

enter image description here

update:

=ARRAYFORMULA(IF(A2:A="",,MMULT(
 TRANSPOSE((ROW(INDIRECT("2:"&MAX((A2:A<>"")*ROW(A2:A))))<=
 TRANSPOSE( ROW(INDIRECT("2:"&MAX((A2:A<>"")*ROW(A2:A))))))*
 (INDIRECT("C2:C"&MAX((A2:A<>"")*ROW(A2:A)))-
  INDIRECT("D2:D"&MAX((A2:A<>"")*ROW(A2:A))))), 
 SEQUENCE(MAX((A2:A<>"")*ROW(A2:A))-1)^0)))

enter image description here

Upvotes: 1

Chris Barrett
Chris Barrett

Reputation: 601

Thanks guys. I tried @player0 method and got an error about array to big. And for some reason @TheMaster version just got stuck on the progress bar. Anyway found this over on the google help forum

=ArrayFormula(if(len(A4:A),(SUMIF(ROW(D4:D),"<="&ROW(D4:D),D4:D)+SUMIF(ROW(C4:C),"<="&ROW(C4:C),C4:C)),))

And seems to work (I have a date in A4:A now). But please let me know if you can see any potential issues

Upvotes: 1

kirkg13
kirkg13

Reputation: 3010

Update: I see that the answer I've adapted from @MattKing, is very similar to @TheMaster's earlier answer, just a variation on how we force the arrayformula to cycle through the cells.

Answer: In addition to player0's solution, I found a related question (where player0 also had the answer!), and adapted a formula answer by @MattKing. Try the following in C1.

={"Total";
ARRAYFORMULA(SUMIF(SEQUENCE(ROWS(A2:A),1),"<="&SEQUENCE(ROWS(A2:A),1,1,1),A2:A) -
             SUMIF(SEQUENCE(ROWS(B2:B),1),"<="&SEQUENCE(ROWS(B2:B),1,1,1),B2:B) )}

Since it was originally designed to sum multiple columns, I'm not sure if I've reduced it as much as might be possible, for your single column implementation.

Upvotes: 0

TheMaster
TheMaster

Reputation: 50383

  • Use two running totals: of IN and OUT and subtract OUT from IN

  • Classic arrayformula solution to create a running total is to use SUMIF with a variable string(relative to the range argument) as a criterion

    =ARRAYFORMULA(SUMIF(ROW(A2:A7),"<="&ROW(A2:A7),A2:A7)-SUMIF(ROW(A2:A7),"<="&ROW(A2:A7),B2:B7))

  • Open range version:

    =ARRAY_CONSTRAIN(ARRAYFORMULA(SUMIF(ROW(A2:A),"<="&ROW(A2:A),A2:A)-SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)),ARRAYFORMULA(LOOKUP(2,1/(A2:A<>""),ROW(A2:A))),1)

Upvotes: 1

Related Questions