Batch Total of numbers between end of batch flags

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.
IT SSMAsked:
Who is Participating?
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:

Add this formula to cell [C2]:

Then add this formula to cell [C3]:

Now, copy cell [C3] & copy down column [C] to the extent of your data (in the case of your sample workbook, cell [C17]).

This results in your "Example Output 2".

Now, the "clever" bit...

I have added Conditional Formatting to column [C], so that if the corresponding entry in column [ B ] is 0, the colo(u)r of the text in column [C] is changed to white.

This now represents your "Example Output 1".

I have updated your workbook, as attached.

Select all of column [C] to see the original formulae.


IT SSMAuthor Commented:
Mate thanks for this simple and does exactely what  I want cheers for the quick help.
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.