Trying to add 7 cells but get to reset when a cell is zero

So you want to sum columns 1 to 7 but from the last zero

Which columns (starting at ? ending at?)
Do the "zero" columns actually contain 0 or are they blank or contain something else (dash, N/A, etc)

So you basically want to sum within a row, from the last number on the right back to either the last 0 or 7 cells whichever is less

Try this, let's say we're working in row 2

=MIN(SUM(INDEX(2:2,LOOKUP(2,1/((2:2=0)*ISNUMBER(2:2)), COLUMN(2:2))):INDEX(2:2, MATCH(99,2:2))), SUM(INDEX(2:2, MATCH(99,2:2)-6):INDEX(2:2, MATCH(99,2:2)))

It sums the numbers from the last 0 to the end and the numbers of the last 7 and takes whichever is less.

Unless something is going wrong, it will always add from the last 0 (or last 7 days if there is no 0 in last 7 days)
If you have a specific issue, try uploading a spreadsheet, (Go Advanced>Manage Attachments)

Okay, just so we can use 1 formula all the way through, In A3 (I put it in A4) copied to the right

=MIN(SUM(INDEX(A2:$A2,COLUMNS($A2:A2)-MIN(COLUMNS($A2:A2)-1,6)):A2), IFERROR(SUM(INDEX($A2:A2,LOOKUP(2,1/(($A2:A2=0)*ISNUMBER($A2:A2)), COLUMN($A2:A2))):A2),900))

Questions or problems?

Glad to help.

Thanks Chemist, for the life of me I couldn't get a formula to work in a single cell