Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save BobRupholdt/2e7da91d0a5156e8e7a153e15f9e27ab to your computer and use it in GitHub Desktop.
Save BobRupholdt/2e7da91d0a5156e8e7a153e15f9e27ab to your computer and use it in GitHub Desktop.
Running total of column B
=ArrayFormula(
IF(ISNUMBER(B2:B),
MMULT(
TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*B2:B),
SIGN(B2:B)
),
IFERROR(1/0))
)
Running total of column B, aggregated by column A for up to 9 distinct values in column A
={"Running Total";
ArrayFormula(
query(
query(
if(
{transpose(unique(indirect("A2:A"&counta($A$2:$A)+1)))} = $A$2:$A,
mmult(
transpose((row($B$2:$B)<=transpose(row($B$2:$B)))*$B$2:$B),
if({transpose(unique(indirect("A2:A"&counta($A$2:$A)+1)))}=$A$2:$A,1,0)
),
0
),
"select "&left(concatenate(transpose("Col"&row(indirect("1:"&countunique($A$2:$A)))&"+")),countunique($A$2:$A)*5-1)&" label "&left(concatenate(transpose("Col"&row(indirect("1:"&countunique($A$2:$A)))&"+")),countunique($A$2:$A)*5-1)&" ''",
0),
"select * limit "&counta(A2:A)
)
)
}
Results in:
A B C
ID Value Running Total
A 1 1
B 10 10
C 100 100
A 1 2
B 10 20
C 100 200
A 1 3
B 10 30
C 100 300
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment