counting blocks of 1's
 

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.

counting blocks of 1's
 

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

On Feb 17, 8:21*pm, "Bryan De-Lara"
wrote:
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.

counting blocks of 1's
 

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.

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

On Feb 17, 8:21 pm, "Bryan De-Lara"
wrote:
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.

counting blocks of 1's
 

=SUMPRODUCT(--ISNUMBER(FIND(1,C2:C512)))


"Bryan De-Lara" wrote:

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.

counting blocks of 1's
 

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.

"Teethless mama" wrote in message
...
=SUMPRODUCT(--ISNUMBER(FIND(1,C2:C512)))


"Bryan De-Lara" wrote:

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.

counting blocks of 1's
 

Try this array formula** :

=COUNT(1/FREQUENCY(IF(A1:A18=1,ROW(A1:A18)),IF(A1:A18<1,RO W(A1:A18))))

screencap:

http://img208.imageshack.us/img208/5...upsof1smm0.jpg

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Bryan De-Lara" wrote in message
...
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.

counting blocks of 1's
 

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

"Bryan De-Lara" wrote:

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.

"Teethless mama" wrote in message
...
=SUMPRODUCT(--ISNUMBER(FIND(1,C2:C512)))


"Bryan De-Lara" wrote:

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.

counting blocks of 1's
 

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

counting blocks of 1's
 

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

counting blocks of 1's
 

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


On Sun, 17 Feb 2008 23:04:24 -0000, "Bryan De-Lara"
wrote:

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

counting blocks of 1's
 

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


On Sun, 17 Feb 2008 23:04:24 -0000, "Bryan De-Lara"
wrote:

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