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.
|