Need to count blank cells b/w two non blank cells

[proficient]

Hi All,

need to know the formula that counts blank b/w two non-blank cells in a rage.

Let's say A1:A14 has below data

Kamran
Blank
Blank
Mumtaz
Blank
Blank
Blank
Ahmed
Blank
Blank
Blank
Blank
Blank
Adnan

In B1 I want 2 as there are two blanks b/w Kamran and Mumtaz, in B4, 3 and B9, 5...

Hope it makes sense!

 

[Yongle]

In B1 copied down

=IFERROR(IF(ISBLANK(A1),"",MATCH("*",A2:A$65000,0)-1),"")

The last cell in lookup range must lie below the last row containing data
- allow lots of spare rows for new data

 

[proficient]

Hi Yongle, thanks for your quick response, There is one more condition that I should have mentioned in the above question, there is another column based on that it should count blanks, here is a sample data for your ease - below;

Here is my Previous data

Kamran
Blank
Blank
Blank
Blank
Mumtaz
Blank
Blank
AhmedBlank
Blank
Blank
Blank
Blank
Blank
Adnan

In column A I have items and In B, I have a list of Sub items, next to every blank cell of column a there is a sub item name in B, I want to count sub items b/w two item...

Kamran has 4 Sub items,
Mumtaz has 2 sub items and so on...

 

[Yongle]

Try my formula in C1 and copy down