Counting number of 1s in a row until blank cell is reached.

[wjhansen]

Hello everyone,

Besides being a casual user of Excel, also the use of a forum is new to me, so if you feel my post doesn't belong here, please advise.

I am currently in an internship for a Dutch research company which investigates the labor market (in this case, unemployment spells), and my job is to combine data from several years and make the data easier to use. However, I am having an issue that I can't seem to resolve by myself, despite Googling for at least 1 hour. Hopefully, I came to the right place.

Anyway, the situation is as follows. My Excel file contains header columns with dates, i.e. Jan2003, Feb2003, Mar2003 etc. If an individual is unemployed in a certain month, the value of the cell is 1, otherwise it's empty. What I eventually want to create is just 2 columns per unemployment spell: one column with the header 'Start of UE Spell 1' and the other 'Duration of UE Spell 1'. The main problem here is duration. I cannot simply sum all 1s, because an individual might have more than one unemployment spell during the observed period. Hence, I want to count the number of 1s in a row until a blank cell is reached.

I did try out the formula in this thread, but somehow it seems to only work when the first 1 is in the very first column.

Probably, my describing skills are not perfect, so I attached a small example. In the cells I marked blue, you find the answer that the correct formula should give me. As I have thousands of observations, it would save me many days of work doing this manually if one of you happens to know a formula that works.

Thank you very very much for any help in advance.

PS. In case it matters, I am using Excel 2010.