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.