What I want to do is have a running total (on a separate sheet) of numbers in a column. All values are 1 or 0; I want the total to reset when there’s a zero.

Any thoughts how?

Ta

Andy

does your running total column run down the B column as if next to your numbers column in A?

In which case simply have code in B2 with list starting at A2:

=if(A2=0, 0, A2+B1)

No – want to have the running total in a fixed position on another worksheet.

Andy

what do you mean by reset?

Or do you mean just to sum the 1s in a column until it gets to the first 0?

Sorry – should have been clearer. What I want to do is total the 1’s until a zero comes up, at which time I want the total reset to zero and then start adding up the ones again.

ie:

1 (total=1)
1 1 (2)
1 1 0 (0)
1 1 0 1 (1)
1 1 0 1 0 (0)
1 1 0 1 0 1 (1)

and so on.

Andy

so in which is your total calculation going to be?
And in which cells are your number sequences?
Do the number sequences run along rows as in your post up there ^?
Do they have the same number of entries in each row?

Assuming the 0s and 1s are in their own columns then try this in cell a2 on sheet 2:

=IF((SUM(Sheet1!A2:Z2))=(SUM(Sheet1!A1:Z1)),0,IF(MOD((SUM(Sheet1!A2:Z2)),2)=0,A1+1,0))

You’ll have to complete the first cell A1 manually on sheet 2 e.g. 1.

I’m gonna guess this is gambling related?

Edit probably need another if maybe an and if it’s odd, somewhere.

depending exactly the format of your data – I would suggest you use the fact that multiplying by 0 and 1 has the desired effect…

…but without seeing exactly how your data is laid out its difficult to be more specific.

I seem to have got it to work using VLOOKUP and an array formula to match today’s date to a running total in a second column.

Thanks all!

Andy