Count blanks between two not blank cells

[a94andwi]

Hello.
I have a column containing values.
Some cells are blank.

If a cell contains a value I would like to count how many blanks that are after the current cell including the current until I have a new value.

Example
Column A: |||Column B: (Result of function)
Row nr: 1: Value 1 |||2
Row nr: 2: |||0
Row nr: 3: Value 1 |||3
Row nr: 4: |||0
Row nr: 5: |||0
Row nr: 6: Value 1 |||.......

Anyone who can help me? (Value 1 above is always a digit)
The ||| divides the 2 columns.


Thanks
Anders

[wsc]

Hi Anders,

You can try this

A|||B |||C |||D
1|||=IF(C2=1,IF(C3=1,C2,D3),0)|||=IF(ISBLANK(A2),C1+1,1)|||=IF(OR(C3=1,ISBLANK(C3)),C2,D3)
|||=IF(C3=1,IF(C4=1,C3,D4),0)|||=IF(ISBLANK(A3),C2+1,1)|||=IF(OR(C4=1,ISBLANK(C4)),C3,D4)
|||=IF(C4=1,IF(C5=1,C4,D5),0)|||=IF(ISBLANK(A4),C3+1,1)|||=IF(OR(C5=1,ISBLANK(C5)),C4,D5)
2|||=IF(C5=1,IF(C6=1,C5,D6),0)|||=IF(ISBLANK(A5),C4+1,1)|||=IF(OR(C6=1,ISBLANK(C6)),C5,D6)
|||=IF(C6=1,IF(C7=1,C6,D7),0)|||=IF(ISBLANK(A6),C5+1,1)|||=IF(OR(C7=1,ISBLANK(C7)),C6,D7)
|||=IF(C7=1,IF(C8=1,C7,D8),0)|||=IF(ISBLANK(A7),C6+1,1)|||=IF(OR(C8=1,ISBLANK(C8)),C7,D8)

[robert111]

your numbers and blanks are in column A

in col B starting at row 2 put

if(a2="",if(a1="",b1+1,2),"")

this sequentially counts blocks of blanks

You should be able in a third column to detect where each sequence ends