CountBlank with a reset?

[mpenkala]

Hey all,

I currently have a running list in columns B-S. The cells either contain an
'x' or is blank.

What I've looking for is a formula that will count the blank cells in a
column, but reset once a 'x' is given. then re-start counting the blank
cells once it occurs again.
ex.

ColumnC
x
x



<----- here it would show "4" as its been blank for 4 rows
x <----- count resets due to it not being blank.
x

<----- here it would have reset to "2" as it's currently been blank for 2
rows


Thanks in advance!
Matt

 

[FSt1]

hi
=countblank(A2:S50)
adjust your your range.

Regards
FSt1

 

[mpenkala]

Hey FSt1,

I'm not looking for somthing to simply count all the blank cells. I need
something that will count the blank cells in a column continuously until an
'x' apprears. Once that happens, I need the count to reset and begin from
scratch again.

ex.

ColB
x
x
(blank)
(Blank)
(blank) <---- here the count formula would give 3
(blank)
(blank) <---- here it would give 5
x
x
x
(blank) <---- here it would give 1 (it reset due to the x showing)
(blank)
(blank)
(blank)
(blank)
(blank) <---- here it would give 6

Any ideas,
Thanks
Matt

 

[T. Valko]

This seems overly complicated to me but it works.

Assume the range of interest is B1:B15.

Enter this formula in C1:

=IF(B1="x","",IF(AND(B1="",B2<>""),1,TEXT(0,";;;")))

Enter this formula in C2 and copy down to C15:

=IF(COUNTIF(B$1:B$15,"x")=0,"",IF(B2="x","",IF(OR(AND(B2="",B3<>""),ROW(C2)=MAX(ROWS(B$1:B$15))),COUNTBLANK(B$1:B2)-SUM(C$1:C1),"")))

 

[T. Valko]

Enter this formula in C1:

=IF(B1="x","",IF(AND(B1="",B2<>""),1,TEXT(0,";;;")))

We can reduce that to:

=IF(B1="x","",IF(AND(B1="",B2<>""),1,""))

 

[mpenkala]

Hey Biff,

Your post got a little split up, so I'm a bit confused.
Lets say the range of interest is B3:B30
I want the Formula (which counts the consecutive blank cells) in U3.

Do I enter
=IF(B3="x","",IF(AND(B3="",B4<>""),1,TEXT(0,";;;")))
In cell U3
and do I enter
=IF(COUNTIF(B$3:B$15,"x")=0,"",IF(B4="x","",IF(OR(AND(B4="",B5<>""),ROW(U4)=MAX(ROWS(B$3:B$15))),COUNTBLANK(B$3:B4)-SUM(U$3:U4),"")))

In cell U4?? Cause that's what I've done and I'm not getting any numbers,
whether I've got x's are blank cells.

Thanks for your help. You may be right, I'm just not reading the post
correctly.

Cheers,
Matt

 

[T. Valko]

If there are no Xs in the range what result do you expect?

 

[T. Valko]

Lets say the range of interest is B3:B30

I want the Formula... in U3.

Enter this formula in U3:

=IF(AND(B3="",B4<>""),1,"")

Enter this formula in U4 and copy down to U30:

=IF(B4="x","",IF(OR(AND(B4="",B5<>""),ROWS(C$3:C4)=ROWS(B$3:B$30)),COUNTBLANK(B$3:B4)-SUM(C$3:C3),""))