count occasions of sickness

hi all,

I need a UDF to help me determine how many occasions of sickness an employee has had. for each week he is sick it's marked as a 1 in my table. the data runs from d2:bb2.

so if my columns looks like

111100000111001000111

the answer would be 4 because he was off sick for 4 weeks the first time then came back and went off sick for another 3 weeks and then back for 2 weeks etc etc

all help appreciated guys. I need to put the answer in cell bc2

thanks again

Maybe:

Please Login or Register  to view this content.

yes nathansav

thanks john, have csn I adapt this to loop through all rows with data as there could be anything from 10 to 1000 employees

Try:

Please Login or Register  to view this content.

Hi.. try this.

I have assumed that the employee names were in Column C.. adapt it to suit.

I have used a helper column (column 85) to transpose each row and then replace the zeros with "" and then count the areas..

The helper column gets cleared after it is used.

Check out the attachment.

Please Login or Register  to view this content.

another option:

Please Login or Register  to view this content.

thanks apo this works

but im getting an error when the number of occassions is 0

how can I fix this

any help appreciated

Hi..

This will take care of the zero's..

Please Login or Register  to view this content.

I tried watersev's code as it looked like a nice idea..

I modified it slightly to iterate through all your rows and also needed to set the range to be the last used column cell or it would add 1 to the count (as there may well be a area between last used value and bb)..

Please Login or Register  to view this content.

Thanks to watersev for this method.. I have never used it before..

Attached is a workbook showing both options.. you will need to clear column BC before running watersevs (if you have already run mine previously)..