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.