counting blocks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bryan De-Lara - 16 Feb 2008 20:43 GMT

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.

Reply to this Message

Bernard Liengme - 16 Feb 2008 20:53 GMT

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

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Could anyone sort out this formula please. I've tried various combinations
> but seem to be stuck.
[quoted text clipped - 10 lines]
>
> Bryan.

Reply to this Message

Bryan De-Lara - 16 Feb 2008 21:11 GMT

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.

>I am short of time but this seems to for me
> Give it a try with some data you can count in your head.
[quoted text clipped - 14 lines]
>>
>> Bryan.

Reply to this Message

Gord Dibben - 16 Feb 2008 21:46 GMT

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 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
[quoted text clipped - 21 lines]
>>>
>>> Bryan.

Reply to this Message

Bryan De-Lara - 16 Feb 2008 22:02 GMT

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.

> Circular reference would come from having the formula within the range of
> cells.
[quoted text clipped - 35 lines]
>>>>
>>>> Bryan.

Reply to this Message