counting blocks of 1's

  • Thread starter Thread starter Bryan De-Lara
  • Start date Start date
B

Bryan De-Lara

Anyone have any suggestions.
I'm trying to count batches of 1's in a column from C4 to C512. Once that is
done I can then move on to the other columns which must be counted
separately.
The columns go from C to DH.
I need to count the blocks going down each column, i.e. 111 11 1 1111
1111 11111 would =6. 11111111 1111=2
The sheet column A is date (month & year) B is day. It must have 2 years
going down for each working day of the 2 years, 512
I can't go across the sheet because of the limitations of cells going
across.
I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<>1)) &
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)).
The second formula seems to add 1 to the count. The first is fine unless
there is only 1 in the column when it returns a 0.
Anybody got a fix, I'd be grateful.

Bryan.
 
You mean that within your cells you could have one or more digits, but
you are only interested in counting those cells which only have 1's in
them, no matter how many times? Have a look at the SUBSTITUTE function
in Excel Help - substitute "" for a "1" and test to see if the length
is zero after applying this.

Hope this helps.

Pete
 
No Pete, the cells will either be blank or 1, If 1 has a blank on either
side it is 1 block. If two or more cells together have 1 and a blank cell
that is another block. So you could have all 512 cells with a 1 and be 1
block, or 25 blocks if blank cells are between. It's the amount of blocks I
need. As it's easy enough to count the individual 1's with =sum that's not
the problem. I need to know so that I can calculate by converting into
points.

Hope you can help. The substitute wasn't much help. Thanks.

Bryan.

You mean that within your cells you could have one or more digits, but
you are only interested in counting those cells which only have 1's in
them, no matter how many times? Have a look at the SUBSTITUTE function
in Excel Help - substitute "" for a "1" and test to see if the length
is zero after applying this.

Hope this helps.

Pete
 
Thanks Teethless mama, that counts the 1's in the row, i.e. 11 11 111=7
which I need to read 3 for 3 blocks of 1's. This of course is going down,
not across.

Bryan.
 
=SUMPRODUCT(--ISNUMBER(FIND(1,1:1)))

Bryan De-Lara said:
Thanks Teethless mama, that counts the 1's in the row, i.e. 11 11 111=7
which I need to read 3 for 3 blocks of 1's. This of course is going down,
not across.

Bryan.
Click to expand...
 
The solution has already been given in microsoft.public.excel.

Please don't post the same question in multiple NGs - especially after you've already been given the answer!!!

Cheers
 
Okay thanks macropod, but none worked, either it adds one or is a circular
reference. Thanks any way. I'll give up on it.
Maybe I'm not explaining it properly.

Bryan.

macropod said:
The solution has already been given in microsoft.public.excel.

Please don't post the same question in multiple NGs - especially after
you've already been given the answer!!!

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
Anyone have any suggestions.
I'm trying to count batches of 1's in a column from C4 to C512. Once that
is done I can then move on to the other columns which must be counted
separately.
The columns go from C to DH.
I need to count the blocks going down each column, i.e. 111 11 1 1111
1111 11111 would =6. 11111111 1111=2
The sheet column A is date (month & year) B is day. It must have 2 years
going down for each working day of the 2 years, 512
I can't go across the sheet because of the limitations of cells going
across.
I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<>1)) &
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)).
The second formula seems to add 1 to the count. The first is fine unless
there is only 1 in the column when it returns a 0.
Anybody got a fix, I'd be grateful.

Bryan.
Click to expand...
Click to expand...
 
You are explaining correctly.

You are not reading properly the replies you received from macropod and myself.

Did you try inserting a blank row at top and entering

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1)) in C1 and dragging across?


Gord Dibben MS Excel MVP


Okay thanks macropod, but none worked, either it adds one or is a circular
reference. Thanks any way. I'll give up on it.
Maybe I'm not explaining it properly.

Bryan.

macropod said:
The solution has already been given in microsoft.public.excel.

Please don't post the same question in multiple NGs - especially after
you've already been given the answer!!!

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
Anyone have any suggestions.
I'm trying to count batches of 1's in a column from C4 to C512. Once that
is done I can then move on to the other columns which must be counted
separately.
The columns go from C to DH.
I need to count the blocks going down each column, i.e. 111 11 1 1111
1111 11111 would =6. 11111111 1111=2
The sheet column A is date (month & year) B is day. It must have 2 years
going down for each working day of the 2 years, 512
I can't go across the sheet because of the limitations of cells going
across.
I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<>1)) &
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)).
The second formula seems to add 1 to the count. The first is fine unless
there is only 1 in the column when it returns a 0.
Anybody got a fix, I'd be grateful.

Bryan.
Click to expand...
Click to expand...
Click to expand...
 
Yes macropod I did, but in the next few rows when it is dragged across say
D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1, (10 rows) it adds 1 to the count. I did
add at the end of the formula -1 to make good, but then it reports -1 if
there was no 1's entered. When a few rows are entered with 1's the rest
behind change to 1 with no data entered. Maybe I am thick, I didn't think
so, but I think this proved it.
Anyway, I'd like to thank everyone who tried to help me, and put up with me,
very kind indeed.
I won't bother anyone anymore.

Bryan.


Gord Dibben said:
You are explaining correctly.

You are not reading properly the replies you received from macropod and
myself.

Did you try inserting a blank row at top and entering

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1)) in C1 and dragging across?


Gord Dibben MS Excel MVP


Okay thanks macropod, but none worked, either it adds one or is a circular
reference. Thanks any way. I'll give up on it.
Maybe I'm not explaining it properly.

Bryan.

macropod said:
The solution has already been given in microsoft.public.excel.

Please don't post the same question in multiple NGs - especially after
you've already been given the answer!!!

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Anyone have any suggestions.
I'm trying to count batches of 1's in a column from C4 to C512. Once
that
is done I can then move on to the other columns which must be counted
separately.
The columns go from C to DH.
I need to count the blocks going down each column, i.e. 111 11 1
1111
1111 11111 would =6. 11111111 1111=2
The sheet column A is date (month & year) B is day. It must have 2
years
going down for each working day of the 2 years, 512
I can't go across the sheet because of the limitations of cells going
across.
I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<>1)) &
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)).
The second formula seems to add 1 to the count. The first is fine
unless
there is only 1 in the column when it returns a 0.
Anybody got a fix, I'd be grateful.

Bryan.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top