counting blocks

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

Bryan De-Lara

Could anyone sort out this formula please. I've tried various combinations
but seem to be stuck.
I'm trying to use this. =SUMPRODUCT((c5:c512=1)*(C5:C512<>1))
I am trying to count blocks of 1's in a column from C5 to C512 but all I get
is a return of 0.
Every cell could be filled with 1 and it should return just 1, or if I put
10 in a row then leave a cell blank then put another 10 1's in the next 10
cells it should return 2.
I'm going across the workbook with 112 columns, from C TO DH.
The idea is from C5 to C512 is every working day for 2 years (2007 & 2008)
with C to DH being employees names.
If anyone could help I'd be very grateful.

Bryan.
 
I am short of time but this seems to for me
Give it a try with some data you can count in your head.
=SUMPRODUCT(--(C1:C10=1),--(C2:C11<>1))
best wishes
 
Thanks for that Bernard, all I get is a circular ref problem. It also needs
to cover the whole 512 cells. It could be up to 30 blocks of 1's and needs
to be automatic as it has to be equal another cell and the N 1's counted
individually (which is simple for even me =sum (c5:c512). then multiplied.
i.e. ? blocks by ? occasions by say 5 blocks x 5 occasions x 20 days S=S D.
5X5X20.
 
Circular reference would come from having the formula within the range of cells.

=SUMPRODUCT(--(C1:C512=1),--(C2:C513<>1)) entered in D1 for testing.

I tested on column C with 50 known blocks of 1's in the C1:C512 range.

Bernard's Formula returned 50


Gord Dibben MS Excel MVP
 
Thanks Gord. I know my problem now, I was entering it in the wrong cell.
Nice when people take the trouble, many many thanks.

Bryan.
 
Back
Top