Counting Consecutive Blanks

[DushiPunda]

Hello,
I'm revisiting an old problem - last thread here: Counting Consecutive Blanks

I'll try to explain the best I can.

The bottom line is - I want to count the number of times there are only 1 or 2 cells (blank or not) in between two cells that contain "24".

Here's a sample of my work schedule:

The column of concern is AJ ("2 DAYS"). Some notes:

1. As you can see from Row 4, "24" is not the only possible value of cells. The expected output (see below) is 3 - so my formula (also below) is counting the L's.
2. Row 5 is a little weird because it's giving an output of 2, while is should be one. This oddity only happens when one of the "24" is in one of the following columns: D, E, AE, AF. For more context - If I put a single "24" in one of those columns, it counts it as a "2 DAY" (i.e. it will put "1" in column AJ). So if I then add another "24" with 1 or 2 cells in between the previous, it will add 1 and give me 2.
3. Rows 6 & 7 are further showcasing the issue explained in #2.

The expected outputs for rows 4-7 are:

Row	2 DAYS
4	3
5	1
6	4
7	4

The current array formula I'm using (I couldn't get the formula from my prior post to work at all):

{=SUM(IF(FREQUENCY(IF(C4:AG4="",COLUMN(C4:AG4)),IF(C4:AG4<>"",COLUMN(C4:AG4)))=2,1,0))+SUM(IF(FREQUENCY(IF(C4:AG4="",COLUMN(C4:AG4)),IF(C4:AG4<>"",COLUMN(C4:AG4)))=1,1,0))}

I found this formula here and modified it to fit my needs (sorta). I am a complete novice when it comes to array formulas, but what I tried to make it do (and it does for the most part) SUM the number of times there are 2 blanks cells with the number of times there is 1 blank cell.

Though it didn't work, I tried the following to account for "non-blank, non-24" values:

{=SUM(IF(FREQUENCY(IF(C4:AG4=24,COLUMN(C4:AG4)),IF(C4:AG4<>24,COLUMN(C4:AG4)))=2,1,0))+SUM(IF(FREQUENCY(IF(C4:AG4=24,COLUMN(C4:AG4)),IF(C4:AG4<>24,COLUMN(C4:AG4)))=1,1,0))}

Instead of looking at blank and non-blank cells as my first formula appears to be doing, I figured this would look at cells that are or are not equal to 24, counting the number of cells that aren't 24, and returning to me the amount of times there are 2 consecutive or 1 "non-24" in between cells that are 24.

Any help is sincerely appreciated!

 

[Fluff]

What version of Excel are you using?

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’)

 

[DushiPunda]

What version of Excel are you using?

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’)

2021. Account details updated.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2
3
4			L			24			24		24			24				24		L	L		24					24			L	L	L	3
5				24		24																												1
6					24			24			24					24						24					24			24		24		4
7					24			24			24					24						24					24			24			24	4
Data

Cell Formulas
Range	Formula
AH4:AH7	AH4	=LET(r,XLOOKUP(24,C4:AG4,C4:AG4):XLOOKUP(24,C4:AG4,C4:AG4,,,-1),s,SEQUENCE(,COLUMNS(r)),SUM(--(FREQUENCY(IF(r<>24,s),IF(r=24,s))={1,2})))

 

[DushiPunda]

This is incredible. Thank you! I ran it through some of our prior month schedules and it's definitely accurate. I did run across one scenario where it gave the results as intended, but not the results I wanted (basically a scheduling scenario that I didn't consider when I posted this originally).

How hard would it be to modify for the following situation:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2
3
4
5					24					24				24				24			24				24			24					7	2
6				24				24					24					24		12		24					24				24		7	0
7
Sheet1

Cell Formulas
Range	Formula
AG5:AG6	AG5	=COUNTIF(C5:AF5, "24")

Row 6 should result in 2 but as the formula is right now, it's showing 0 (again, expected based on the conditions I originally gave).

I guess the easy way to put it is - instead of just looking for "24" how do we look for "12" or "24"?

Thanks again for your help, I absolutely wouldn't have figured this out on my own.

Oh, and a note: The formula isn't in my mini sheet above as I'm posting this from my home computer but the worksheet is on my work computer.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1
2
3
4
5					24					24				24				24			24				24			24					7	2	2
6				24				24					24					24		12		24					24				24		7	0	2
Data

Cell Formulas
Range	Formula
AG5:AG6	AG5	=COUNTIF(C5:AF5, "24")
AI5:AI6	AI5	=LET(r,C5:AG5,rr,INDEX(r,,MIN(IFNA(XMATCH({12,24},r),999)+1)):INDEX(r,,MAX(IFNA(XMATCH({12,24},r,,-1),0)-1)),s,SEQUENCE(,COLUMNS(rr)),SUM(--(FREQUENCY(IF((rr<>24)+(rr=12),s),IF((rr=24)+(rr=12),s))<3)))

 

[Eric W]

Another slightly shorter option (including the 12/24 update):

=IFERROR(LET(t,IF(C4:AG4={12;24},COLUMN(C4:AG4)),s,SEQUENCE(COUNT(t)-1),SUM(--(SMALL(t,s+1)-SMALL(t,s)={2,3}))),0)

 

[DushiPunda]

@Fluff - All worked, but in one of my rows (28) there is only a single "24" (in cell G28) and it was giving me a 2 instead of 0, not sure why as I don't know how to read these formulas.
@Eric W - This solution, as far as I can tell, worked all around for all of the conditions I've specified.

Thank you both for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[felixstraube]

Just for fun, here is another option:

Prueba1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1
2
3
4			L			24			24		12			24				12		L	L		24					24			L	L	L		3
5				24		24																													1
6					24			24			24					24						24					24			24		24			4
7					24			24			24					24						24					24			24			24		4
8			24		24			24				24			24		24		12			12				24		24			24		24		9
9				24					24				24				24			24				24			24								2
10			24				24					24					24		12		24					24				24					2
Sheet1

Cell Formulas
Range	Formula
AI4:AI10	AI4	=LET(d, C4:AG4, s, SEQUENCE(COLUMNS(d)), md, TEXTJOIN("",,(d<>24)*(d<>12)), sd, MID(md, s, LEN(md) - s + 1), count, IFERROR(FIND("0", sd, 2) - 2, 0), sc, MID(md, s, 1), r, count*(sc="0")*1, SUM((r>0)*(r<3)) )