Excel: How to count how many times a consecutive text value appears

Question

I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row.

I got this spreadsheet where I make those schedules but I didn't find a way to have visual feedback if I broke that rule or not.

I already tried countif but it doesn't work for me since it is expected that a specific value would be shown multiple times, what I need to know is how many times the value appears consecutively, and to tell me when that value gets to 7 or >, anything between 6 or less it's ok.

I need a formula to tell me that so I could put it in the end column as conditional formatting to have a fast visible feedback about it.

Here's a Google Sheets link to what I need to do:

https://docs.google.com/spreadsheets/d/1Dz4d3_H-yBGLdh3orloGY_14vPVSnLV73OlFxwnDnM8/edit?usp=sharing

Answer 1

I can speak for Excel, not really sure about Google docs‥ So:

1. Under names I put zeros, something to start with.
2. Right to the zeros a formula =IF(B3="On",A7+1,0)
   where B3 is the first cell for Joseph and A7 is the starting zero.
3. Formula is distributed till the end.
4. I then put conditional formula >6 to all the newly created cells.

Conditional formatting might be tricky to see on a big table, you may also use max() on the whole matrix.

UPD: I've done it in your google sheet.