Well, You seem to have yourself quite the interesting problem.

This would be straightfoward if you were trying to sum from the top. But summing between the TRUEs is quite complicated.

Here is the formula I wrote to make it happen:

`=IF(ROW()=1,"",IF(AND(A2,NOT(INDIRECT(CONCAT("A",MAX(ROW()-1,1))))),SUM(INDIRECT(CONCAT("B",IF(ISNUMBER(MATCH(2,1/(A$1:INDIRECT(CONCAT("A",ROW()-1))))),MATCH(2,1/(A$1:INDIRECT(CONCAT("A",ROW()-1))))+1,1))):B2),""))`

So yah... Really gross.

This could likely be simplified with array formulas, but i'm personally not a fan of them.

Also, this is not a very *portable* formula, as you need to edit the column references, both in text and reference. So...

Here is an even more gross version that uses just indirect to use relative cell references:

`=IF(ROW()=1,"",IF(AND(INDIRECT(CONCAT("R",ROW(),"C",COLUMN()-2),FALSE),NOT(INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE))),SUM(INDIRECT(CONCAT("R",IF(ISNUMBER(MATCH(2,1/(INDIRECT(CONCAT("R1C",COLUMN()-2),FALSE):INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE)))),MATCH(2,1/(INDIRECT(CONCAT("R1C",COLUMN()-2),FALSE):INDIRECT(CONCAT("R",ROW()-1,"C",COLUMN()-2),FALSE)))+1,1),"C",COLUMN()-1),FALSE):INDIRECT(CONCAT("R",ROW(),"C",COLUMN()-1),FALSE)),""))`

^ This formula can be dropped in any cell (without edit) and will sum the column to left of it based on the column 2-to-the-left (as shown in my image).

## Explaination

Alright, buckle your seatbelts, lets break it down.

First off, the real hero in this equation is:
`match(2,1/([range]=TRUE))`

This searches bottom-to-top in a selected range and finds the first TRUE

Now, we need the ranges for our match function to use. For this we use `indirect`

, which takes a text string interprets it as a reference.
So, for the range that each row needs to look at, we say:

`=A$1:indirect(concat("A",row()-1))`

If this formula is in `C5`

, it would produce the reference range: `A$1:A4`

Now we can find the most recent TRUE:

`=match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))`

Okay, we have the row number for the latest TRUE, lets sum up the range of match+1 and current row:

`=sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))+1)):B1)`

*(This *`B1`

would change as we fill the column)

Well, thats grand! We have a functioning formula. But we only want it to output when TRUE. So lets slap an IF statement on the front to output `""`

when the A column is FALSE:

`=if(A1=false,"",sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))):B1))`

Cool, now if we test out formula, we find that we have some issues...
namely in row 2. This is because our match function requires an range arguments, and we provide `A$1:A1`

when on the first row. So, lets add another IF:

`=if(row()=1,if(A1,B1,""),=if(A1=false,"",sum(indirect(concat("B",match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))):B1)))`

So now the formula handles row 1 on its own... But also, We can see that if we happen to have `A1`

set to FALSE, we get an error on row 2. This is because the `match`

function didn't find a TRUE above it. So we need to check for "N/A" and replace the value with 1 if it is N/A (since we want to sum to the top). This requires another IF. Lets break it out of the formula to see it alone:

`=if(isna(match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE))),1,=match(2,1/(A$1:indirect(concat("A",row()-1))=TRUE)))`

Great! Now slap that all together, and you have the first formula above.

*Note: Some changes have been made, but the explanation hasn't been updated. Sorry*

For the relative reference version, just replace all hard-references to `A1`

,`A2`

,`B2`

,`"B"`

, etc. with indirect functions... And Voila!

### TLDR:

Use `match(2,1/[range])`

and `indirect`

to check for the most recent TRUE and then sum from there to current row. Also, handle annoying edge cases.