Counting contiguous blocks of data

StrangeFascination

New Member
Joined
Mar 4, 2009
Messages
6
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,632
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top