**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi all,
Having a little drama with a roster redesign I'm doing.
Each employee has a row, 365 cells long, representing the entire year. Days off are marked "X", while days worked are filled with a numerical start time.
To ensure the employees are rostered as equally as possible, I would like a way to count the number of blocks of >7 consecutive shifts. What this means is the number of times there is at least 7 cells between two cells which contain "X".
The count is then used in a separate sheet to compare against other employees.
I've nutted out how to count the number of weekend shifts for my other analysis, but this one has me stumped.
Any help appreciated!

Solved by M. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
18/12/2017 - 06:26

hello

Excelchat Expert
18/12/2017 - 06:26

Thank you for choosing got it pro

Excelchat Expert
18/12/2017 - 06:26

how may I help you today

User
18/12/2017 - 06:26

the description above covers the problem.

Excelchat Expert
18/12/2017 - 06:27

I got it

Excelchat Expert
18/12/2017 - 06:27

do you have a file to share?

User
18/12/2017 - 06:27

just pasted some into the right panel

Excelchat Expert
18/12/2017 - 06:27

Okay

Excelchat Expert
18/12/2017 - 06:27

Let me just review everything here

User
18/12/2017 - 06:28

I need a formula to count along a row and give me the # of times there are blocks of >7 cells between cells which contain "X"

User
18/12/2017 - 06:29

So in the case to the right the result should be 1.

Excelchat Expert
18/12/2017 - 06:30

I just need to make sure I understand this

Excelchat Expert
18/12/2017 - 06:31

For ex. on week 1, I do see 2 "x", will that be counted as 1?

Excelchat Expert
18/12/2017 - 06:32

Or anything that is less than 7 in a single row is counted as one?

User
18/12/2017 - 06:32

I want to count how many times there are 7 or more cells containing anything other that "x" between cells which contain "x"

User
18/12/2017 - 06:34

so, it'd check along row 7. when it finds a cell containing "X", it counts the number of cells along the row until it finds another cell containing "X". Then, if it's 7 or more cells between "X", it increments the total by 1.

Excelchat Expert
18/12/2017 - 06:35

okay to confirm, 7X is equivalent to 1, correct?

User
18/12/2017 - 06:36

7 or more cells in a row (between "X"s) count as 1

Excelchat Expert
18/12/2017 - 06:36

7X in a row is = 1 count, is that right?

User
18/12/2017 - 06:36

no, 7*(anything other than X) = 1

Excelchat Expert
18/12/2017 - 06:38

is there any other number that is in the row aside from 7 and X?

Excelchat Expert
18/12/2017 - 06:39

Okay, I think I got it now

User
18/12/2017 - 06:40

there's X, which means day off

User
18/12/2017 - 06:40

and then a number between 0-24, which represent the start times of the shift

Excelchat Expert
18/12/2017 - 06:41

ex: from G7 the number of cells with "7" before tha next cell with "X" is 8, so that should be counted as 1, is that correct?

User
18/12/2017 - 06:41

yes, that's ight

User
18/12/2017 - 06:41

right

Excelchat Expert
18/12/2017 - 06:42

okay, got it

Excelchat Expert
18/12/2017 - 06:42

give me few minutes to figure this out

User
18/12/2017 - 06:42

thanks man

Excelchat Expert
18/12/2017 - 06:42

please extend the chat while I am working on this

User
18/12/2017 - 06:42

how do I do that?

Excelchat Expert
18/12/2017 - 06:43

I will try to create the formula then I'll explain it once done

User
18/12/2017 - 06:43

ok

User
18/12/2017 - 06:46

Row 7 should return 1

User
18/12/2017 - 06:46

Row 9 should return 2

User
18/12/2017 - 06:46

just for example

Excelchat Expert
18/12/2017 - 06:46

the whole row?

Excelchat Expert
18/12/2017 - 06:47

ok I got it

Excelchat Expert
18/12/2017 - 06:47

cause row 7 contain more than 7 working days

Excelchat Expert
18/12/2017 - 06:47

got that

User
18/12/2017 - 06:47

yep

User
18/12/2017 - 06:48

and row 9 has two blocks of >7 shifts in a row

Excelchat Expert
18/12/2017 - 06:48

still trying to create formula functions

Excelchat Expert
18/12/2017 - 06:48

got it

Excelchat Expert
18/12/2017 - 06:55

give me few more minutes please

User
18/12/2017 - 06:56

no worries

User
18/12/2017 - 07:03

just extended again

Excelchat Expert
18/12/2017 - 07:03

Almost there

Excelchat Expert
18/12/2017 - 07:21

May I ask, the number of weeks in single row should be a maximum of 4, correct?

User
18/12/2017 - 07:21

no, would be 52

User
18/12/2017 - 07:21

the whole year

User
18/12/2017 - 07:22

on my sheet, the year runs from column F to column NE

Excelchat Expert
18/12/2017 - 07:23

ok, so your putting the whole year in a single row?

User
18/12/2017 - 07:23

yes

Excelchat Expert
18/12/2017 - 07:24

Okay since we only have few minutes left

Excelchat Expert
18/12/2017 - 07:24

Can I send you the solution via email?

Excelchat Expert
18/12/2017 - 07:24

If so can I have your email address

User
18/12/2017 - 07:24

please do

User
18/12/2017 - 07:25

email is stringerdylanm@gmail.com

Excelchat Expert
18/12/2017 - 07:25

what is your email

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*