Created
January 10, 2019 15:57
-
-
Save BobRupholdt/2e7da91d0a5156e8e7a153e15f9e27ab to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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