View Single Post
  #7   Report Post  
Pat
 
Posts: n/a
Default

Did you mean to enter it like this:
=OFFSET(Centre!$C$77,0,0,SUMPRODUCT(--(C77:C65536<""))

or to enter like this:
=OFFSET(Centre!$C$77,0,0,SUMPRODUCT(Centre!(C77:C6 5536<""))

Either way I have not been successful.

Pat



"Peo Sjoblom" wrote in message
...
You can use this instead of counta

SUMPRODUCT(--(C77:C65536<""))


Regards,


Peo Sjoblom

"Pat" wrote:

You obviously have data in C1:C77 thus it will be counted, you can use

Yes you are correct.

This has now eliminated many empty cells. I say many because where there

is
an empty cell with a formula the range includes these. The data finishes

at
C807 but the formula continues on to C1000. Can the defined range be

made
to ignore a formula range on a sheet?

Pat


"Peo Sjoblom" wrote in message
...
You obviously have data in C1:C77 thus it will be counted, you can use

=OFFSET(Centre!C$77,0,0,COUNTA(Centre!$C77:$C65536 ),1))


Regards


Peo Sjoblom


"Pat" wrote:

The defined range below extends the range beyond the cells where the

data
ends.
The start of the range is Centre!$C$77 the end of the range is C1054.
The
last cell containing data is C807. The data in C is the result of a
formula
and is in the range C77:C1000

=OFFSET(Centre!$C$77,0,0,COUNTA(Centre!$C:$C),1)

Anyone familiar with all of this?
Thanks if you can help.
Pat