# Excel - COUNT Function Problem - Expert Solution

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
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
User 18/12/2017 - 07:24
User 18/12/2017 - 07:25
email is stringerdylanm@gmail.com
Excelchat Expert 18/12/2017 - 07:25

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.