Count Blocks of occurances within a range

Hello All,

This is my first posting here. I have a problem I have been trying to solve for some weeks now.

I have a sheet for recording staff sickness etc. I can count the number of days a member of staff is sick or late in a range, but i need to count the number of periods of sick in the same range.

I have attached an example. In cell AB4 i want to count the number of Periods of sick (3 in this case) - in order that later I can calculate bradford factor.

I would really be very grateful for some pointers or guidance guys.

Thank you!

Andy Brown

Thank you so much guys! Apologies for the late response, I've been off sick.

Both solutions work perfectly, but we will use the function.

Much appreciated, this has been bugging me for ages!

Hello All,

I open this thread again because for the next FY my reporting requirements have changed.

mrice wrote me a nice little function (see above) that I have been using over the last year, however now I need to be able to do the same thing, but counting "S" as well as "\"

"S" represents a whole day sick "\" represents a half day.

So i need to count the number of periods of sickness in a period not just the number of days.

therefore if there a 3 "S" followed by a "\" or vice versa this needs to be counted as one period of sickness.

I hope this makes sense, and once again I am more than happy to donate a little something to anyone who can help provide a solution.

Thanks again in anticipation!

Andy

Thank you so much again!! Your modification works perfectly. Having spent most of yesterday having a go myself, I don't think I was far off, just couldn't get the logic and the brackets in the right places.

Once again you've saved me a huge amount of work. Very much appreciated. Good luck on the 24th April!

Andy