I have a spreadsheet with 2 columns. No and Flag. The No column is a series of numbers which I need to count in batchs. The Flag columns denotes the end of a batch with a -1 the rest of the time the flag is 0. I need to total the numbers corresponding to the zero's and the -1 then the zero's to the next -1 this list may be 20 rows long or 65000 rows long. The -1 will not always be evenly spaced. But the zeros and the next -1 determine a batch. The series can start on on a -1 or 0 depending on when the data snapshot is taken.
I dont care if the count is incremental for the batch but must restart the count on the next zero after the -1. IE if the first matching number and 0 flag is 11 then the count is 11.
I have attached a spreadsheet with some sample data. I dont have time to muck around working this out and will be solving a more complex issue. I would prefer output example 1 but can work with output example 2 in the sample spreadsheet. I have saved the sample data spreadsheet in 97-2003 format but am using 2010 if that helps.
Thanks for your help in advance.