Sum adjacent cells in a range until a cell with a different value is detected

Hi,

I am new in the forum and I have looked up similar topics but couldn't find the answer that would suit me. I am stuck in doing one analysis, not able to find the right formula, so here is the issue:

I have a set of binary values in my file (for simplicity I have reduced the range to A2:K7, while in the real file I would have around 1500 rows for each date of 2020 and 2021). On the right (L2:V7) there is the desired outcome and the formula I am looking for should do the following: once the value in the raw data set is 1, it should evaluate the adjacent cells on the left and should check how many consequent 1 values there are and sum them. Once it encounters a cell with 0 it should not count further. E.g in the raw 7 in my file when it evaluates the cell K7 with a simple IF statement - IF(K7=1, ..., 0) --- it should sum the values up to cell F7 because in E7 there is a zero and it should stop the evaluation. In V7 it should reflect the sum.

I tried SUMPRODUCT but wasn't really successful. Does anybody have an idea here?

Sorry, I tried reading your paragraph but don't really understand.

Give pasting this formula in cell M4 and it should match what you had, not sure if it's what you're looking for though. Note that wednesday needs to be left as 0s, need to deal with that edge case.

=IF(AND(B4=1,A4=1),L4+B4,0)

L4:L7 would all be 0 since there are no cells to the left of A4:A7.

M4: =B4*(L4+A4)

Copy M4, select M4:V7, paste.

Hey, thanks a lot, both seem to be working. I knew it was supposed to be simple, but it was incorporated in a larger analysis with more complex formulas, so I was convinced at a certain point I needed something complicated here too