Count how many blank cells before cell value

[bluefeather8989]

I need a formula that will Count how many blank cells before cell value.
Example R15:R21 there are 2 blanks before the cell R17 with a value .

 

[SanjayGMusafir]

Try this in S17 and below - But it has some limitations. It shall count all blanks above the cell

=Countifs($R$15:R16,"")

 

[Peter_SSs]

Will there always be at least one cell in R15:R21 that is not blank?

See if this does what you want.

=XMATCH("?*",R15:R21&"",2)-1

 

[SanjayGMusafir]

Hi @Peter_SSs
Your solution sounded interesting and new to me. So thought giving it a try to learn...

Am I missing something here?

Book2
	A	B	C
1	Test
2	Test
3	Test		0
4
5
6	Test
7	Test
8	Test
Sheet1

Cell Formulas
Range	Formula
C3	C3	=XMATCH("?*",A1:A8&"",2)-1

 

[Peter_SSs]

Am I missing something here?

I believe you are, though the OP can confirm one way or the other.
My reading is that the OP wants to know how many blank cells in the range before a non-blank cell

I need a formula that will Count how many blank cells before cell value.
Example R15:R21 there are 2 blanks before the cell R17 with a value .

In your example there are no blank cells before a cell with value in the range A1:A8 therefore I believe the correct answer is zero as per the formula.

 

[SanjayGMusafir]

In your example there are no blank cells before a cell with value in the range A1:A8 therefore I believe the correct answer is zero as per the formula.

Interesting. Thanks for reverting. It was useful.

 

[bluefeather8989]

Will there always be at least one cell in R15:R21 that is not blank?

See if this does what you want.

=XMATCH("?*",R15:R21&"",2)-1

This is exactly what i need. THANKS

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.