Counting contiguous blocks of data

[StrangeFascination]

Hi everyone,

I am creating a spreadsheet which tracks sickness levels of staff. The spreadsheet is laid out with each agent representing a row, and the dates running across the top. When an agent is sick, a 1 is placed in the corresponding cell for that day.

What I need is a way to count the number of occurences as well as days. For example, if an agent has four days off in a row, this would be represented as a contiguous block of 4 cells, each containing a 1. If later in the year they have another 3 days off in a row, similarly this would be another contiguous block of 3 cells, each containing a 1. This would represent 7 days sick, but only 2 occurences. I'd like a formula which enables me to count the number of these occurences across the year.

Many Thanks in advance.

 

[Aladin Akyurek]

Hi everyone,

I am creating a spreadsheet which tracks sickness levels of staff. The spreadsheet is laid out with each agent representing a row, and the dates running across the top. When an agent is sick, a 1 is placed in the corresponding cell for that day.

What I need is a way to count the number of occurences as well as days. For example, if an agent has four days off in a row, this would be represented as a contiguous block of 4 cells, each containing a 1. If later in the year they have another 3 days off in a row, similarly this would be another contiguous block of 3 cells, each containing a 1. This would represent 7 days sick, but only 2 occurences. I'd like a formula which enables me to count the number of these occurences across the year.

Many Thanks in advance.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:L2=1,COLUMN(A2:L2)),IF(A2:L2<>1,COLUMN(A2:L2))),1))

which counts blocks of 1.

On the other hand:

=COUNTIF(A2:L2,1)

yields a day count.

 

[StrangeFascination]

Thanks a lot, this seems to work well. How would I modify this to count contiguous blocks of 1s and 0s? I want to enter 0s to represent weekends, so that if someone has a sickness that spans multiple weeks, this is seen as 1 occurence, but the counting the days doesn't include saturday and sunday. I would simply place weekdays in the spreadsheet, but we have some staff who work saturdays and sundays and some who don't