Counting Consecutive Blanks

[DushiPunda]

Hello,
I'm looking for a way to count the number of times TWO consecutive blanks cells occur on my spreadsheet (work schedule) here is an example of the intended output:

​

B​

C​

D​

E​

F​

G​

H​

I​

..​

AD​

AE​

AF​

AK​

1​

1​

2​

3​

4​

5​

6​

7​

8​

..​

29​

30​

31​

# 2 Days​

2​

24​

​

24​

​

24​

​

24​

​

..​

24​

​

24​

4​

3​

24​

​

​

24​

​

​

24​

​

..​

​

​

24​

2​

4​

24​

​

​

24​

​

​

​

24​

..​

​

24​

​

1​

The data I need to look at exists in columns B:AF; currently I have a bunch of hidden helper cells using the following formulas:

Starting with:

=IF(COUNTIF(B6:E6,24)>=2,1,0)

Ending with:

=IF(COUNTIF(AC6:AC6,24)>=2,1,0)

Basically looking at consecutive 4 day chunks from the schedule.

The issue: If there are any ONE day breaks, these are counted as 2 TWO day breaks, whereas they should be counted as 1 TWO day break. Below is a copy of the above example with the outputs that I'm currently getting.

	B	C	D	E	F	G	H	I	..	AD	AE	AF	AK​
1	1	2	3	4	5	6	7	8	..	29	30	31	# 2 Days​
2	24		24		24		24		..	24		24	8​
3	24			24			24		..			24	2​
4	24			24				24	..		24		1​

I tried to solve this by using an imbedded IF to look at the next helper cell's 4 day reference and came up with this (not really sure how this logic works, but it's checked out in my testing):

Starting with:

=IF(AND(COUNTIF(B6:E6,24)>=2,COUNTIF(C6:F6,24)>=2),0,IF(AND(COUNTIF(B6:E6,24)>=2,COUNTIF(C6:F6,24)<>2),1,0))

Ending with:

=IF(AND(COUNTIF(AC6:AF6,24)>=2,COUNTIF(AD6:AG6,24)>=2),0,IF(AND(COUNTIF(AC6:AF6,24)>=2,COUNTIF(AD6:AG6,24)<>2),1,0))

This has largely solved my issues, but gets a little janky if there is a one day break at the end of the month.

Surely there is an easier way to do this? Thanks!

 

[jasonb75]

Your examples and descriptions don't match up very clearly, see if this helps.

=SUM(--(FREQUENCY(IF($B2:$AF2="",$B$1:$AF$1),IF($B2:$AF2<>"",$B$1:$AF$1))=2))

Note that if you are using excel 2019 (or older) then you will need to array confirm the formula with Ctrl Shift Enter.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)