How to count interstitial blank cells?

[[email protected]]

How can I count the number of blank cells between
the row above and the last nonblank row above, if
the cell in this row is nonblank?

That is, the count is one or more only in the first
nonblank cell following one or more blank cells.

For example, suppose A1:A2 are nonblank, A3:A5
are blank, and A6:A9 are nonblank. I want B6 to
count 3 and B10 to count 4. All other cells in B:B
should be blank (or zero).

I know I can do this easily in VBA.

Is there some way to write a cell formula that only
uses built-in Excel functions -- preferrably not even
add-ins?

[Bryan Hessey]

testing column A, and using column C as a helper column, in C2 put

=IF(A2<>"","",IF(C1="",1,C1+1))

and in B2 put

=IF(A3="","",C2)

and formula drag B2 and C2 down to cover your data.

--

How can I count the number of blank cells between
the row above and the last nonblank row above, if
the cell in this row is nonblank?

That is, the count is one or more only in the first
nonblank cell following one or more blank cells.

For example, suppose A1:A2 are nonblank, A3:A5
are blank, and A6:A9 are nonblank. I want B6 to
count 3 and B10 to count 4. All other cells in B:B
should be blank (or zero).

I know I can do this easily in VBA.

Is there some way to write a cell formula that only
uses built-in Excel functions -- preferrably not even
add-ins?

[[email protected]]

Bryan Hessey wrote:
> testing column A, and using column C as a helper column,
> in C2 put
> =IF(A2<>"","",IF(C1="",1,C1+1))
> and in B2 put
> =IF(A3="","",C2)
> and formula drag B2 and C2 down to cover your data.

Well, I think I screwed up my example, but I got the idea.
Works great! Thanks.

[Bryan Hessey]

Good to see it works for you, and thanks for the response

--

Bryan Hessey wrote:
> testing column A, and using column C as a helper column,
> in C2 put
> =IF(A2<>"","",IF(C1="",1,C1+1))
> and in B2 put
> =IF(A3="","",C2)
> and formula drag B2 and C2 down to cover your data.

Well, I think I screwed up my example, but I got the idea.
Works great! Thanks.