CountBlank with a reset?

  • Thread starter Thread starter mpenkala
  • Start date Start date
M

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
 
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
 
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),"")))
 
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
 
Lets say the range of interest is B3:B30
I want the Formula... in U3.
Click to expand...

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),""))
 
Back
Top