Number of Blank Cells Between the Last and Second to Last Non-Blank Values

[RuRu_11]

Kindly advise me what formula to use in order to find out how many blank cells are between the last and second-to-last non-blank cells in Excel column.

 

[dalvin647]

Hi RuRu_11.

It's awful, but it kind of works.

=LET(f,SUMPRODUCT(MAX((A1:A100<>"")*ROW(A1:A100))),f-SUMPRODUCT(MAX((INDIRECT("A1:A"&f-1)<>"")*ROW(INDIRECT("A1:A"&f-1)))))-1

This formula works for the A Column from A1:A100.

Book1
	A	B
1	1	10
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9
10
11
12
13	13
14
15
16
17
18
19
20
21
22
23
24	24
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LET(f,SUMPRODUCT(MAX((A1:A100<>"")*ROW(A1:A100))),f-SUMPRODUCT(MAX((INDIRECT("A1:A"&f-1)<>"")*ROW(INDIRECT("A1:A"&f-1)))))-1

 

[Coach_KM]

This is great, but what if I want to also find a third number of blank cells? As in the number between A13 and A8?

 

[Peter_SSs]

Welcome to the MrExcel board!

This lists the blanks between each non-blank in the column, starting at the bottom. Is that something that you could use?

23 07 16.xlsm
	A	B
1	a	9
2		0
3	c	0
4		3
5	e	2
6		1
7		1
8	h
9
10
11
12	l
13	m
14	n
15
16
17
18
19
20
21
22
23
24	x
Blanks

Cell Formulas
Range	Formula
B1:B7	B1	=LET(d,SORT(FILTER(ROW(A1:A24),A1:A24<>""),,-1),s,SEQUENCE(ROWS(d)-1),INDEX(d,s)-INDEX(d,s+1)-1)
Dynamic array formulas.

 

[Coach_KM]

Thank you! Yes, this did give me a great start. Although when I used the formula it omitted the 0 values. Ultimately I was hoping to use it within another formula, but I never could have gotten to this point.

 

[Peter_SSs]

when I used the formula it omitted the 0 values.

Do you mean like column B below?
.. or column C below?
.. or something else?

23 08 04.xlsm
	A	B	C
1	a	9	9
2			3
3	c		2
4		3	1
5	e	2	1
6		1
7		1
8	h
9
10
11
12	l
13	m
14	n
15
16
17
18
19
20
21
22
23
24	x
Sample

 

[Coach_KM]

Yes, column C is what I get. Which is acceptable since I'll really only have max 4 values to let spill. I just have to adjust for that in the spreadsheet.

 

[Peter_SSs]

Yes, column C is what I get.

Hmm, I can't see how that formula could produce a result like that - unless you don't have any consecutive non-blank cells in the range.

Are you able to post a small sample like mine (with the formula inserted) with XL2BB so I might be able to investigate further.

 

[Coach_KM]

The "spaces" between are all formulas, so technically not blank. The count is correct between the cells with characters.

GreenLake Summer Extravaganza 2023.xlsx
	O	P	Q	R
3	**** R	Eddie E	Eric J	Glenn M
4	2	3	2	2
5	10	8	10	10
6		16
7
8
9
10
11
12		A
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28	A		B	A
29		A
30
31
32
33
34
35
36
37
38		A
39	C		B	A
40
41
42
43
44
Race schedule

Cell Formulas
Range	Formula
O3:R3	O3	=OFFSET(Lineups!$AA$2,(COLUMN()-10),0)
O4:R4	O4	=COUNTIF(O7:O42,"a")+COUNTIF(O7:O42,"b")+COUNTIF(O7:O42,"c")+COUNTIF(O7:O42,"d")
O5,Q5:R5,P5:P6	O5	=IF(O4>1,LET(d,SORT(FILTER(ROW(O7:O43),O7:O43<>""),,-1),s,SEQUENCE(ROWS(d)-1),INDEX(d,s)-INDEX(d,s+1)-1),"")
O7:R42	O7	=IFERROR(IF(COUNTIF(INDIRECT($D7&$E7&$F7&LEFT($G7,1)&"a"),O$3),"A",IF(COUNTIF(INDIRECT($D7&$E7&$F7&LEFT($G7,1)&"B"),O$3),"B",IF(COUNTIF(INDIRECT($D7&$E7&$F7&LEFT($G7,1)&"C"),O$3),"C",IF(COUNTIF(INDIRECT($D7&$E7&$F7&LEFT($G7,1)&"D"),O$3),"D","")))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:AJ45	Cell Value	="A"	text	NO
J7:AJ45	Cell Value	="B"	text	NO
J7:AJ45	Cell Value	="C"	text	NO
J7:AJ45	Cell Value	="D"	text	NO
J7:AJ45	Cell Value	="E"	text	NO
J7:AJ45	Cell Value	="F"	text	NO
J7:AJ45	Cell Value	="x"	text	NO
J4:AK4	Cell Value	=1	text	NO
J4:AK4	Cell Value	=2	text	NO
J4:AK4	Cell Value	>2	text	NO

 

[Peter_SSs]

I can't see that the formula is missing any zero values for that data. The formula that you requested was to count the blank rows between non-blank rows.
I have highlighted those rows green below and the formulas in row 5 have counted them correctly, starting from the bottom.
To get a zero value in the result (zero rows between two non-zero rows) would naturally require two adjacent cells in the column to contain non-zero values. Your example does not have that situation.

23 08 05.xlsm
	O	P	Q	R
3	**** R	Eddie E	Eric J	Glenn M
4	2	3	2	2
5	10	8	10	10
6		16
7
8
9
10
11
12		A
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28	A		B	A
29		A
30
31
32
33
34
35
36
37
38		A
39	C		B	A
40
41
42
43
Count Blanks

Cell Formulas
Range	Formula
O5,Q5:R5,P5:P6	O5	=IF(O4>1,LET(d,SORT(FILTER(ROW(O7:O43),O7:O43<>""),,-1),s,SEQUENCE(ROWS(d)-1),INDEX(d,s)-INDEX(d,s+1)-1),"")