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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bryan De-Lara" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>

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.


"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "Bryan De-Lara" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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.
>>
>
>

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


On Sat, 16 Feb 2008 21:11:40 -0000, "Bryan De-Lara"
<(E-Mail Removed)> wrote:

>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.
>
>
>"Bernard Liengme" <(E-Mail Removed)> wrote in message
>news:%(E-Mail Removed)...
>>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
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "Bryan De-Lara" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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.
>>>
>>
>>

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.


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> On Sat, 16 Feb 2008 21:11:40 -0000, "Bryan De-Lara"
> <(E-Mail Removed)> wrote:
>
>>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.
>>
>>
>>"Bernard Liengme" <(E-Mail Removed)> wrote in message
>>news:%(E-Mail Removed)...
>>>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
>>> --
>>> Bernard V Liengme
>>> Microsoft Excel MVP
>>> http://people.stfx.ca/bliengme
>>> remove caps from email
>>>
>>> "Bryan De-Lara" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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.
>>>>
>>>
>>>
>