CountBlank with a reset?
 

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

CountBlank with a reset?
 

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

Regards
FSt1

"mpenkala" wrote:

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

CountBlank with a reset?
 

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


"FSt1" wrote:

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

Regards
FSt1

"mpenkala" wrote:

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

CountBlank with a reset?
 

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))),COU NTBLANK(B$1:B2)-SUM(C$1:C1),"")))

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

CountBlank with a reset?
 

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))),COU NTBLANK(B$1:B2)-SUM(C$1:C1),"")))

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

CountBlank with a reset?
 

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))),COU NTBLANK(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" wrote:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))),COU NTBLANK(B$1:B2)-SUM(C$1:C1),"")))

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

CountBlank with a reset?
 

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

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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))),COU NTBLANK(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" wrote:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))),COU NTBLANK(B$1:B2)-SUM(C$1:C1),"")))

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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

CountBlank with a reset?
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
If there are no Xs in the range what result do you expect?

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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))),COU NTBLANK(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" wrote:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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))),COU NTBLANK(B$1:B2)-SUM(C$1:C1),"")))

--
Biff
Microsoft Excel MVP


"mpenkala" wrote in message
...
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