Im trying to find a single formula for cells in Column K, that will return the sum of cells in Column J up to when the row in col I was last TRUE. Can't seem to solve this puzzle. Any ideas?

Ive highlighted examples and what I am expecting to see in Col K as seen below

CodePudding user response：

Use IF to see if the Value is true they use sumifs to sum all the FALSE and then subtract what is already be accounted for.

Put this in K2 and copy down.

CodePudding user response：

=--IF(INDEX(I:I,ROW())=TRUE,SUM(INDEX(J:J,MAX(IF(((I:I=TRUE)*(ROW(I:I)<ROW())>0),ROW(I:I) 1,1))):INDEX(J:J,ROW()-1)))

For versions prior to Office 365 enter this as array formula (confirm with ctrl shift enter)

It checks if the cell in column I in the current row is TRUE. If FALSE it returns 0, If TRUE, it returns the sum of the range in column J from the first row after the latest found TRUE in column I up to the current row -1 (if there's no prior TRUE in column I it starts from row 1).