Count the number of consecutive occurrences

NBVC,

Thanks for your help, I think I have got the formula working now. I used the first formula you gave me making some slight adjustments.

Thanks

Hi there!

I don't know if you are still at this address but here's trying.

Could you please forward me your spreadsheet and maybe just an explanation on how you use/d it? I have been tasked to look at absenteeism and using the Bradford Index although we are looking at a period of 26 weeks in which the absenteeism takes place and it seems what you did will help me a great deal.

Thanking you in advance,
Rennier
South Africa

morning,

I have a question that is somewhat related to the above. I have values in several columns, each value can only occur once per row. I'm trying to find out two things:
1/ what is the longest consecutive occurrence by a particular value, say '23' within a certain date range;
2/ what are the top 3 (or 5) consecutive occurrences by any value within the same date range.

In other words, what I need to find out is this: 'the number 23 has occurred 7 times consecutively from 1-Mar-09 through whatever (dates are in the column A). And the longest consecutive occurrences have been made by numbers 'x', which has occurred so many times, from such and such date through such and such date. The next number that did this, was 'y', which occurred some many times, etc, etc.

any help is appreciated,

thanks,

L