Google Sheets - Running Total with reset
Ok I have the following data from my brokerage:
| Amount | Description |
|---|---|
| -0.5 | EXCHANGE FEE |
| -0.09 | CLEARING FEE |
| -0.25 | COMMISSIONS |
| 20 | TRADE PAIRED |
| -0.5 | EXCHANGE FEE |
| -0.09 | CLEARING FEE |
| -0.25 | COMMISSIONS |
| 10 | TRADE PAIRED |
What I would like to do is having a running total that resets after it encounters "TRADE PAIRED" in the Description column. So it would look like this:
| Amount | Description | Running Total |
|---|---|---|
| -0.5 | EXCHANGE FEE | -0.5 |
| -0.09 | CLEARING FEE | -0.59 |
| -0.25 | COMMISSIONS | -0.84 |
| 20 | TRADE PAIRED | 19.16 |
| -0.5 | EXCHANGE FEE | -0.5 |
| -0.09 | CLEARING FEE | -0.59 |
| -0.25 | COMMISSIONS | -0.84 |
| 10 | TRADE PAIRED | 9.16 |
Anyone have an ARRAYFORMULA that would be able to do this? I've been to do a continuous running total but it's not resetting after "TRADE PAIRED"
=ArrayFormula(if((G5:G<>"TRADE PAIRED"),MMULT(IF(ROW(D5:D)>=TRANSPOSE(ROW(D5:D))=TRUE,1,0),if(G5:G<>"TRADE PAIRED",n(D5:D),0)),MMULT(IF(ROW(D5:D)>=TRANSPOSE(ROW(D5:D))=TRUE,1,0),if(G5:G<>"TRADE PAIRED",n(D5:D),0))+D5:D)
This is not a duplicate of existing: ArrayFormula of Resetting Running Total in Google Sheets
As that one is grouping based off a common value. Mine is resetting based on a specific value. Additionally, the formula provided in the link crashes Google Sheets
try:
=INDEX(IF(A:A="",,MMULT(--TRANSPOSE(IF((TRANSPOSE(ROW(A:A))>=ROW(A:A))*(
{0; ARRAY_CONSTRAIN(COUNTIFS(B:B, "TRADE PAIRED",
ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)}=TRANSPOSE(
{0; ARRAY_CONSTRAIN(COUNTIFS(B:B, "TRADE PAIRED",
ROW(B:B), "<="&ROW(B:B)), ROWS(A:A)-1, 1)})),A:A, 0)), ROW(A:A)^0)))
- Google script for move rows depending on checkbox - Google sheet
- Getting a “Google hasn’t verified this app” warning when creating a trigger - ok to ignore it?
- Fixing 1 row, and leaves each following row movable
- How to pass a range as a function parameter?
- Auto pulling a value in a formula to the next value in Google Sheets
- How to Extend a Formula to Apply to All Rows Using MAP or ARRAYFORMULA?
- Google Sheet - copy range if match
- shortest formula to get the last non zero value from column
- Copying data from a sheet to another with conditions
- How can I automatically generate due dates in Google Sheets based on payment plans?
- If one cell IS NOT EMPTY put text on another cell in Google Sheets
- How can I create a simple duplicate detector?
- Authorization is revoked every week. How to maintain persistent authorization?
- Conditional borders in Google Sheets
- Automatically adding a row to a sheet with data from another sheet once every day
- Adding borders based on grouped rows and conditional formatting in Google Sheets
- Illegal Argument Error Thrown When Trying to setTextStyle() for Rich Text in Google Apps Script
- Passing variables to time based triggers
- Google Apps script to conditional format cell border
- Multiple dropdown questions on a google-form tied to spreadsheet
- Google Apps Script sort stopped working "Exception: This operation is not supported on a table header row."
- count number of cells with precise number of repeated characters
- How can I extract columns based on multiple column value/criteria?
- How to count distinct column values in Google Sheets?
- Pivot table (calculated field)
- How do I get Google Script to read the display value of a sheet's cell? It keeps returning some random date instead
- Google Sheets - make a list of the names in column A where column E is greater than zero
- Allocating Specific Leaves and getting Calculation
- Randomize cells in Google Sheets
- Google sheet insertImage function stopped working for Google Apps Script: verify image error