Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countblank formula | Excel Worksheet Functions | |||
COUNTBLANK MAX question | Excel Discussion (Misc queries) | |||
COUNTBLANK function | Excel Worksheet Functions | |||
COUNTBLANK function | Excel Worksheet Functions | |||
COUNTBLANK function | Excel Worksheet Functions |